﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.DirectoryServices;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.Web;
using System.Web.Services;
using Shuba.Business;
using Shuba.Model;

namespace Shuba.Web.ws
{
    /// <summary>
    /// Summary description for mobile
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class mobile : System.Web.Services.WebService
    {
        const string FORMAT_DATE_TIME = "yyyy-MM-dd HH:mm:ss";

        private readonly DateTime MIN_DATE = new DateTime(2009, 1, 1);

        private DateTime? CDate(string date, string format)
        {
            DateTime result;
            if (DateTime.TryParseExact(date, format,
                System.Globalization.CultureInfo.InvariantCulture,
                DateTimeStyles.None, out result))
            {
                return result != DateTime.MinValue ? result : (DateTime?)null;
            }

            return null;
        }

        private DateTime? CDate(string date)
        {
            return CDate(date, FORMAT_DATE_TIME);
        }

        bool IsLDAPUser(string LDAPUser, string LDAPPass)
        {
            if (basePage.ByPassAD())
                return true;

            try
            {
                string LDAPServer = ConfigurationManager.AppSettings["ldap"];

                DirectoryEntry entry = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass);
                string EName = entry.Name;
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        private bool Authenticate(string nip, string password, string imei, string token)
        {
            MobileUserInfo user = GetUser(nip);
            if (user == null)
                throw new Exception("User tidak ada");

            if (token != null && IsValidToken(user.aoCode, token, imei))
                return true;

            if (IsLDAPUser(nip, password))
            {
                CreateToken(user.aoCode, nip, imei);
                return true;
            }
            else throw new Exception("Gagal login ke LDAP Server, Cek User Name/Password atau konfigurasi LDAP");
        }

        private string GetFileSizeDesc(long size)
        {
            const int byteConversion = 1024;
            double bytes = Convert.ToDouble(size);

            if (bytes >= Math.Pow(byteConversion, 3)) //GB
            {
                return string.Concat(Math.Round(bytes / Math.Pow(byteConversion, 3), 2), " GB");
            }
            else if (bytes >= Math.Pow(byteConversion, 2)) //MB
            {
                return string.Concat(Math.Round(bytes / Math.Pow(byteConversion, 2), 2), " MB");
            }
            else if (bytes >= byteConversion) //KB
            {
                return string.Concat(Math.Round(bytes / byteConversion, 2), " KB");
            }
            else //Bytes
            {
                return string.Concat(bytes, " Bytes");
            }
        }

        private void CreateUserDevice(string aoCode, string imei, string nip)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("INSERT INTO TBL_MS_USER_DEVICE(AO_CODE, NIP, IMEI,IS_LOGIN,ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE) VALUES(@AO_CODE, @NIP, @IMEI,0,@NIP, GETDATE(), @NIP, GETDATE())");

            db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
            db.AddInParameter(cmd, "@IMEI", DbType.String, imei);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);

            db.ExecuteNonQuery(cmd);
        }

        public string CreateToken(string aoCode, string nip, string imei)
        {
            MobileUserInfo user = GetLoginInfo(aoCode);
            if (user == null)
                CreateUserDevice(aoCode, nip, imei);

            string token = Guid.NewGuid().ToString();
            DateTime expiredDate = DateTime.Now.AddDays(1);

            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_MS_USER_DEVICE SET IMEI = @IMEI, NIP = @NIP, TOKEN = @TOKEN, EXPIRED = @EXPIRED, UPDATE_BY = @NIP, UPDATE_DATE = GETDATE() WHERE AO_CODE = @AO_CODE");

            db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
            db.AddInParameter(cmd, "@IMEI", DbType.String, imei);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@TOKEN", DbType.String, token);
            db.AddInParameter(cmd, "@EXPIRED", DbType.DateTime, expiredDate);

            db.ExecuteNonQuery(cmd);
            return token;
        }

        public string CreateTokenBAK(string nip, string imei)
        {
            string token = Guid.NewGuid().ToString();
            DateTime expiredDate = DateTime.Now.AddDays(1);

            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_MS_USER_DEVICE SET TOKEN = @TOKEN, EXPIRED = @EXPIRED WHERE NIP = @NIP AND IMEI = @IMEI");

            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@IMEI", DbType.String, imei);
            db.AddInParameter(cmd, "@TOKEN", DbType.String, token);
            db.AddInParameter(cmd, "@EXPIRED", DbType.DateTime, expiredDate);

            db.ExecuteNonQuery(cmd);
            return token;
        }

        [WebMethod]
        public bool IsValidToken(string aoCode, string imei, string token)
        {
            DateTime processDate = GetProcessingDate();

            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT 1 FROM TBL_MS_USER_DEVICE WHERE AO_CODE = @AO_CODE AND TOKEN = @TOKEN AND IMEI = @IMEI AND EXPIRED > @EXPIRED");

            db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
            db.AddInParameter(cmd, "@IMEI", DbType.String, imei);
            db.AddInParameter(cmd, "@TOKEN", DbType.String, token);
            db.AddInParameter(cmd, "@EXPIRED", DbType.DateTime, processDate);

            bool isValid = false;
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                isValid = reader.Read();
                reader.Close();
            }

            return isValid;
        }

        private string GetSourceCode(string branchID)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT SOURCE_CODE FROM TBL_MS_BRANCH WHERE BRANCH_ID = @BRANCH_ID");

            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branchID);

            string sourceCode = null;

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        sourceCode = (string)reader[0];
                    }
                }

                reader.Close();
            }
            return sourceCode;
        }

        private List<string> GetImeiList(string branchID)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT IMEI FROM TBL_MS_BRANCH_DEVICE WHERE BRANCH_ID = @BRANCH_ID");

            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branchID);

            List<string> list = new List<string>();
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        list.Add((string)reader[0]);
                    }
                }

                reader.Close();
            }
            return list;
        }

        private string GetProductType(int productCode)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT PRODUCT_TYPE FROM TBL_MS_PRODUCTS WHERE PRODUCT_ID = @PRODUCT_ID");

            db.AddInParameter(cmd, "@PRODUCT_ID", DbType.Int32, productCode);

            string type = null;
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    type = reader.SafeGet<string>("PRODUCT_TYPE");
                }

                reader.Close();
            }
            return type;
        }

        private MobileUserInfo GetLoginInfo(string aoCode)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_USER_DEVICE WHERE AO_CODE = @AO_CODE");

            db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
            MobileUserInfo user = null;
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    user = new MobileUserInfo();
                    user.nip = reader.Get<string>("NIP");
                    user.aoCode = aoCode;
                    user.isLogin = reader.Get<bool>("IS_LOGIN");
                    user.lastLogin = reader.SafeGet<DateTime>("LAST_LOGIN");
                    return user;
                }
                reader.Close();
            }

            return user;
        }

        public bool IsValidLoginMobile(string aoCode, ref DateTime lastLogin)
        {
            MobileUserInfo user = GetLoginInfo(aoCode);
            lastLogin = user.lastLogin;
            int min = DateTime.Now.Subtract(user.lastLogin).Minutes;

            int refreshTime = 5;
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 ISNULL(REFRESH_TIME, 5) REFRESH_TIME FROM TBL_SYS_PARAMS (NOLOCK)");
            object result = db.ExecuteScalar(cmd);
            if (result != null) refreshTime = Convert.ToInt16(result);

            return !user.isLogin || (user.isLogin && min >= refreshTime);
        }

        [WebMethod]
        public bool IsLogin(string nip)
        {
            DateTime dateTime = DateTime.MinValue;
            MobileUserInfo info = GetUser(nip);
            if (info == null)
                return false;

            return !IsValidLoginMobile(info.aoCode, ref dateTime);
        }

        [WebMethod]
        public string SetLogin(string aoCode)
        {
            MobileResultInfo info = new MobileResultInfo();
            try
            {
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_MS_USER_DEVICE SET IS_LOGIN = 1, LAST_LOGIN = GETDATE() WHERE AO_CODE = @AO_CODE");
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);

                db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                info.error = ex.Message;
            }

            string content = Serialize(info);
            return content;
        }

        [WebMethod]
        public string Logout(string aoCode)
        {
            MobileResultInfo info = new MobileResultInfo();
            try
            {
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_MS_USER_DEVICE SET IS_LOGIN = 0, LAST_LOGOUT = GETDATE() WHERE AO_CODE = @AO_CODE");
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);

                info.result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                info.error = ex.Message;
            }

            string content = Serialize(info);
            return content;
        }

        private bool IsValidLogin(string nip, bool checkSPADA, ref string app)
        {
            //AppParam param = new AppParam();
            //AppParamInfo appParamInfo = param.Get();
            //short timeOut = appParamInfo.TIME_OUT;
            //short refreshTime = appParamInfo.REFRESH_TIME;
            //DateTime serverDate = param.GetDateDb();

            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT IS_LOGIN, LOGIN_DATE, APP_ID FROM V_USER WHERE NIP = @NIP");

            db.AddInParameter(cmd, "@NIP", DbType.String, nip);

            bool valid = true;

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {

                    app = reader.Get<string>("APP_ID");

                    if (!checkSPADA && app == "SPADA")
                        continue;

                    bool isLogin = reader.Get<string>("IS_LOGIN") == "1";
                    //DateTime loginDate = reader.SafeGet<DateTime>("LOGIN_DATE");

                    //int period = serverDate.Subtract(loginDate).Minutes;

                    /*
                    valid = !isLogin || (isLogin && period >= refreshTime);
                   */

                    valid = !isLogin;

                    if (!valid)
                        break;
                }

                reader.Close();
            }

            return valid;
        }

        [WebMethod]
        public MobileUserInfo LoginFromSPADA(string nip, string password, string imei, bool checkSPADA)
        {
            /*
             * Logic
             * - Auth nip dan password ke LDAP
             * - Cek apakah user sudah di daftarkan ke user application
             * - Cek apakah imei device terdaftar untuk unit user
             * - Cek apakah user sudah login. Jika logout tidak benar cek sudah lewat refresh time.
             * - Cek apakah user sudah login di aplikasi lain(SPADA, SPAWEB)            
             */

            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();

            string encCode = Encryption.Decrypt(ConfigurationManager.AppSettings["enc.code.spada"]);

            if (!IsLDAPUser(nip, password))
                throw new Exception("Gagal login ke LDAP Server, Cek User Name/Password atau konfigurasi LDAP");

            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 * FROM V_USER_MOBILE AND U.NIP = @NIP");

            db.AddInParameter(cmd, "@NIP", DbType.String, nip);

            MobileUserInfo user = GetUser(nip);
            if (user == null)
                throw new Exception(string.Format("User {0} belum terdaftar untuk aplikasi spamobile", nip));

            List<string> imeiList = GetImeiList(user.unitCode);
            string sourceCode = GetSourceCode(user.unitCode);

            if (imeiList.Count == 0)
                throw new Exception(string.Format("Belum ada imei terdaftar untuk branch {0}", user.unitCode));

            user.sourceCode = sourceCode;

            if (!imeiList.Contains(imei))
                throw new Exception(string.Format("Imei {0} tidak terdaftar untuk branch {1}", imei, user.unitCode));

            if (GetLoginInfo(user.aoCode) == null)
                CreateUserDevice(user.aoCode, imei, nip);
            DateTime lastLogin = DateTime.MinValue;
            if (!IsValidLoginMobile(user.aoCode, ref lastLogin))
            {
                String msg = String.Format("Tidak bisa Login, user {0} sudah Login atau Logout dengan tidak benar.", nip);
                if (lastLogin != DateTime.MinValue)
                    msg += " Login terakhir pada " + lastLogin.ToString(FORMAT_DATE_TIME);

                throw new Exception(msg);
            }

            string appid = null;
            if (!IsValidLogin(user.nip, checkSPADA, ref appid))
            {
                string msg = String.Format("Tidak bisa Login, user {0} sudah Login atau Logout dengan tidak benar di aplikasi ", nip);
                if (string.IsNullOrEmpty(appid))
                    msg += "lain";
                else
                    msg += appid;
                throw new Exception(msg);
            }

            string token = CreateToken(user.aoCode, nip, imei);
            user.token = token;
            user.imei = imei;

            FillSysParams(user);

            //KSOAP2: sometimes spamobile failed to read the result.
            //SetLogin(user.aoCode);

            return user;
        }

        private MobileUserInfo GetUser(string nip)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 * FROM V_USER_MOBILE WHERE NIP = @NIP");

            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            MobileUserInfo user = null;
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    user = new MobileUserInfo();
                    user.nip = nip;
                    user.id = reader.Get<int>("USER_ID").ToString();
                    user.aoCode = reader.Get<string>("AO_CODE");
                    user.userName = reader.Get<string>("USER_NAME");
                    user.unitCode = reader.Get<string>("BRANCH_ID");
                    user.emailAddress = reader.Get<string>("EMAIL_ADDRESS");
                    user.processDate = GetProcessingDate().ToString("yyyy-MM-dd");
                    user.aoType = reader.SafeGet<string>("AO_TYPE");
                }

                reader.Close();
            }

            return user;
        }

        [WebMethod]
        public string Login(string nip, string password, string imei)
        {
            MobileResultInfo info = new MobileResultInfo();
            try
            {
                info.result = LoginFromSPADA(nip, password, imei, true);
            }
            catch (Exception ex)
            {
                info.error = ex.Message;
            }

            return Serialize(info);
        }


        [WebMethod]
        public string GetProducts()
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                List<MobileProductInfo> list = new List<MobileProductInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(
                    "SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_TYPE, IS_ACTIVE FROM TBL_MS_PRODUCTS");

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileProductInfo info = new MobileProductInfo();
                        info.id = reader.Get<short>(0).ToString();
                        info.name = reader.Get<string>(1);
                        info.productType = reader.Get<string>(2);
                        info.isActive = reader.Get<string>(3);
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetChannel(string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT CHANNEL_ID, CHANNEL_NAME, IS_ACTIVE FROM TBL_MS_CHANNEL                 
                                WHERE UPDATE_DATE > @UPDATE_DATE";
                List<MobileChannelInfo> list = new List<MobileChannelInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileChannelInfo info = new MobileChannelInfo();
                        info.id = reader.Get<string>("CHANNEL_ID");
                        info.name = reader.Get<string>("CHANNEL_NAME");
                        info.isActive = reader.Get<string>("IS_ACTIVE");
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetFlagFreeze(string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT FLAG, FIELD, ENABLED, IS_ACTIVE FROM TBL_MS_FLAG_FREEZE                
                                WHERE UPDATE_DATE > @UPDATE_DATE";
                List<MobileFlagFreezeInfo> list = new List<MobileFlagFreezeInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileFlagFreezeInfo info = new MobileFlagFreezeInfo();
                        info.flag = reader.Get<short>("FLAG");
                        info.field = reader.Get<string>("FIELD");
                        info.enabled = reader.Get<string>("ENABLED");
                        info.isActive = reader.Get<string>("IS_ACTIVE");
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetExternalMapping(string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT EXTERNAL_ID, CHANNEL_ID, IS_ACTIVE FROM TBL_MS_EXTERNAL_MAPPING                 
                                WHERE UPDATE_DATE > @UPDATE_DATE";
                List<MobileExternalMappingInfo> list = new List<MobileExternalMappingInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileExternalMappingInfo info = new MobileExternalMappingInfo();
                        info.externalID = reader.Get<string>(0);
                        info.channelID = reader.Get<string>(1);
                        info.isActive = reader.Get<string>(2);
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetHolidays(string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT HOLIDAY_DATE, HOLIDAY_NAME, IS_ACTIVE FROM TBL_MS_HOLIDAY
                                WHERE UPDATE_DATE > @UPDATE_DATE";

                List<MobileHolidayInfo> list = new List<MobileHolidayInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileHolidayInfo info = new MobileHolidayInfo();
                        info.holidayDate = reader.Get<DateTime>(0).ToString(FORMAT_DATE_TIME);
                        info.holidayName = reader.Get<string>(1);
                        info.isActive = reader.Get<string>(2);
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetUpdateTable(string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                d.AddDays(-10);
                string sql = @"SELECT TABLE_NAME, FIELD_NAME, DATA_TYPE, IS_TABLE_NEW FROM TBL_UPDATE_TBL_SPAMOBILE (NOLOCK) WHERE ENTRY_DATE > @ENTRY_DATE";

                List<MobileUpdateTableInfo> list = new List<MobileUpdateTableInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileUpdateTableInfo info = new MobileUpdateTableInfo();
                        info.tableName = reader.Get<string>("TABLE_NAME");
                        info.fieldName = reader.Get<string>("FIELD_NAME");
                        info.dataType = reader.Get<string>("DATA_TYPE");
                        info.isTableNew = reader.Get<bool>("IS_TABLE_NEW") ? "1" : "0";
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetStandards(string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT S.MS_STD_GRP_ID, S.MS_STD_ID, S.MS_STD_NAME, S.MS_STD_TYPE, S.IS_ACTIVE
                                FROM TBL_MS_STANDARD S  
                                WHERE S.UPDATE_DATE > @UPDATE_DATE";
                List<MobileStandardInfo> list = new List<MobileStandardInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileStandardInfo info = new MobileStandardInfo();
                        info.group = reader.Get<short>("MS_STD_GRP_ID").ToString();
                        info.value = reader.Get<string>("MS_STD_ID");
                        info.text = reader.Get<string>("MS_STD_NAME");
                        info.type = reader.Get<string>("MS_STD_TYPE");
                        info.isActive = reader.Get<string>("IS_ACTIVE");
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            string content = Serialize(result);

            //File.WriteAllText("D:\\standard.json.gzip", content);

            return content;
        }

        [WebMethod]
        public string GetBranchZip(string unit, string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT ZIP_CODE, KODYA_KAB, KECAMATAN, KELURAHAN
                                FROM TBL_MS_BRANCH_ZIP  WHERE BRANCH_ID = @UNIT_CODE AND UPDATE_DATE > @UPDATE_DATE";
                List<MobileAreaInfo> list = new List<MobileAreaInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileAreaInfo info = new MobileAreaInfo();
                        info.zip = reader.Get<string>(0);
                        info.kodya = reader.Get<string>(1);
                        info.kecamatan = reader.Get<string>(2);
                        info.kelurahan = reader.Get<string>(3);
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetBranch(string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT BRANCH_ID, BRANCH_NAME
                                FROM TBL_MS_BRANCH  WHERE UPDATE_DATE > @UPDATE_DATE";
                List<MobileBranchInfo> list = new List<MobileBranchInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileBranchInfo info = new MobileBranchInfo();
                        info.branchID = reader.Get<string>(0);
                        info.branchName = reader.Get<string>(1);
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        private void FillSysParams(MobileUserInfo user)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 GU_FUP, GU_CLOSE_DEAL, PURGING_PERIOD, PROCESS_DATE, TIME_OUT FROM TBL_SYS_PARAMS  ORDER BY UPDATE_DATE DESC");

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    user.guFup = reader.Get<byte>("GU_FUP");
                    user.guCloseDeal = reader.Get<byte>("GU_CLOSE_DEAL");
                    user.purgingPeriod = reader.Get<byte>("PURGING_PERIOD");
                    user.processDate = reader.Get<DateTime>("PROCESS_DATE").ToString("yyyy-MM-dd");
                    user.timeOut = (int)reader.Get<byte>("TIME_OUT");
                }
                else
                {
                    user.guFup = 7;
                    user.guCloseDeal = 14;
                    user.purgingPeriod = 60;
                    user.processDate = DateTime.Today.ToString("yyyy-MM-dd");
                    user.timeOut = 60;
                }

                reader.Close();
            }
        }

        [WebMethod]
        public string GeneratePRN(string unitCode, string segmentCode, string prospectType, string debtorType, string channeling, string custName, string isNegativeChecking)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Database db = CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("GENERATE_PRN_NEW");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode.Trim());
                db.AddInParameter(cmd, "@SEGMENT_CODE", DbType.String, segmentCode.Trim());
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospectType.Trim());
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, debtorType.Trim());
                db.AddInParameter(cmd, "@CHANNEL_ID", DbType.String, channeling.Trim());
                db.AddInParameter(cmd, "@NAM_CUST", DbType.String, custName);
                db.AddInParameter(cmd, "@IS_CHECK_NEGATIVE_FILE", DbType.String, isNegativeChecking.Trim());

                cmd.CommandTimeout = 1800;
                result.result = db.ExecuteScalar(cmd).ToString();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetUserZip(string aoCode, string date)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                DateTime d = DateTime.Parse(date);
                string sql = @"SELECT ZIP_CODE, KODYA_KAB, KECAMATAN, KELURAHAN
                                FROM TBL_MS_USER_ZIP  WHERE AO_CODE = @AO_CODE AND UPDATE_DATE > @UPDATE_DATE";
                List<MobileAreaInfo> list = new List<MobileAreaInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, d);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileAreaInfo info = new MobileAreaInfo();
                        info.zip = reader.Get<string>(0);
                        info.kodya = reader.Get<string>(1);
                        info.kecamatan = reader.Get<string>(2);
                        info.kelurahan = reader.Get<string>(3);
                        list.Add(info);
                    }

                    reader.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetMaintenanceVisit(string unitCode, string aoCode, string updateDate, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                DateTime updDate = DateTime.Parse(updateDate);

                string sql = @"SELECT VISIT_ID, PROSPECT_ID, AO_CODE, UNIT_CODE, DATE_VISIT,
                                NO_ACCOUNT, DEBTOR_NAME, OBJECTIVE_OF_VISIT,
                                ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE,
                                UPLOAD_BY, UPLOAD_DATE,IS_UPLOAD, IS_DELETED,RECORD_ID,
                                EXPIRY_DATE, EXTEND_DATE, EXTEND_REASON, BATCH_ID, PROOF_CALL,
                                HASIL_PROOF_CALL, CIF, KONTAK_LOG, ISNULL(JENIS_KONTAK,'')JENIS_KONTAK, DATA_TYPE,
                                EXTEND_DATE_2, NIPTeller
                                FROM TBL_MAINTENANCE_VISIT WHERE AO_CODE = @AO_CODE AND UNIT_CODE = @UNIT_CODE AND UPDATE_DATE > @UPDATE_DATE";

                List<MobileMaintenanceVisitInfo> list = new List<MobileMaintenanceVisitInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, updDate);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileMaintenanceVisitInfo info = new MobileMaintenanceVisitInfo();
                        info.id = reader.Get<string>("VISIT_ID");
                        info.aoCode = reader.Get<string>("AO_CODE");
                        info.unitCode = reader.Get<string>("UNIT_CODE");
                        info.dateVisit = reader.Get<DateTime>("DATE_VISIT").ToString(FORMAT_DATE_TIME);
                        info.noAccount = reader.Get<string>("NO_ACCOUNT");
                        info.debtorName = reader.Get<string>("DEBTOR_NAME");
                        info.objectiveOfVisit = reader.Get<string>("OBJECTIVE_OF_VISIT");
                        info.entryBy = reader.Get<string>("ENTRY_BY");
                        info.entryDate = reader.Get<DateTime>("ENTRY_DATE").ToString(FORMAT_DATE_TIME);
                        info.updateBy = reader.Get<string>("UPDATE_BY");
                        info.updateDate = reader.Get<DateTime>("UPDATE_DATE").ToString(FORMAT_DATE_TIME);
                        info.uploadBy = reader.Get<string>("UPLOAD_BY");
                        info.uploadDate = reader.Get<DateTime>("UPLOAD_DATE").ToString(FORMAT_DATE_TIME);
                        info.isUpload = reader.Get<string>("IS_UPLOAD");
                        info.isDeleted = reader.Get<string>("IS_DELETED");
                        info.expiryDate = reader.Get<DateTime>("EXPIRY_DATE").ToString(FORMAT_DATE_TIME);
                        info.recordId = reader.Get<string>("RECORD_ID");
                        info.extendDate = reader.Get<DateTime>("EXTEND_DATE").ToString(FORMAT_DATE_TIME);
                        info.extendReason = reader.Get<string>("EXTEND_REASON");
                        info.batchId = reader.Get<string>("BATCH_ID");
                        info.proofCall = reader.Get<string>("PROOF_CALL");
                        info.hasilProofCall = reader.Get<string>("HASIL_PROOF_CALL");
                        info.cif = reader.Get<string>("CIF");
                        info.kontakLog = reader.Get<string>("KONTAK_LOG");
                        info.jenis_kontak = reader.Get<string>("JENIS_KONTAK");
                        info.dataType = reader.Get<string>("DATA_TYPE");
                        info.extendDate2 = reader.Get<DateTime>("EXTEND_DATE_2").ToString(FORMAT_DATE_TIME);
                        info.nipTeller = reader.Get<string>("NIPTeller");
                        
                        list.Add(info);
                    }
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string GetMaintenanceVisitKp(string unitCode, string aoCode, string updateDate, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                DateTime updDate = DateTime.Parse(updateDate);

                string sql = @"SELECT VISIT_ID, PROSPECT_ID, AO_CODE, UNIT_CODE, DATE_VISIT,
                                NO_ACCOUNT, DEBTOR_NAME, OBJECTIVE_OF_VISIT,
                                ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE,
                                UPLOAD_BY, UPLOAD_DATE,IS_UPLOAD, IS_DELETED,RECORD_ID,
                                EXPIRY_DATE, EXTEND_DATE, EXTEND_REASON, BATCH_ID, PROOF_CALL,
                                HASIL_PROOF_CALL, CIF, KONTAK_LOG, JENIS_KONTAK, DATA_TYPE,
                                EXTEND_DATE_2, NIPTeller, IS_DOWNLOADED
                                FROM TBL_MAINTENANCE_VISIT_SPAWEB WHERE AO_CODE = @AO_CODE AND UNIT_CODE = @UNIT_CODE AND UPDATE_DATE > @UPDATE_DATE AND IS_DOWNLOADED=0";

                List<MobileMaintenanceVisitKpInfo> list = new List<MobileMaintenanceVisitKpInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, updDate);

                using (IDataReader reader = db.ExecuteReader(cmd))
                {
                    while (reader.Read())
                    {
                        MobileMaintenanceVisitKpInfo info = new MobileMaintenanceVisitKpInfo();
                        info.id = reader.Get<string>("VISIT_ID");
                        info.aoCode = reader.Get<string>("AO_CODE");
                        info.unitCode = reader.Get<string>("UNIT_CODE");
                        info.dateVisit = reader.Get<DateTime>("DATE_VISIT").ToString(FORMAT_DATE_TIME);
                        info.noAccount = reader.Get<string>("NO_ACCOUNT");
                        info.debtorName = reader.Get<string>("DEBTOR_NAME");
                        info.objectiveOfVisit = reader.Get<string>("OBJECTIVE_OF_VISIT");
                        info.entryBy = reader.Get<string>("ENTRY_BY");
                        info.entryDate = reader.Get<DateTime>("ENTRY_DATE").ToString(FORMAT_DATE_TIME);
                        info.updateBy = reader.Get<string>("UPDATE_BY");
                        info.updateDate = reader.Get<DateTime>("UPDATE_DATE").ToString(FORMAT_DATE_TIME);
                        info.uploadBy = reader.Get<string>("UPLOAD_BY");
                        info.uploadDate = reader.Get<DateTime>("UPLOAD_DATE").ToString(FORMAT_DATE_TIME);
                        info.isUpload = reader.Get<string>("IS_UPLOAD");
                        info.isDeleted = reader.Get<string>("IS_DELETED");
                        info.expiryDate = reader.Get<DateTime>("EXPIRY_DATE").ToString(FORMAT_DATE_TIME);
                        info.recordId = reader.Get<string>("RECORD_ID");
                        info.extendDate = reader.Get<DateTime>("EXTEND_DATE").ToString(FORMAT_DATE_TIME);
                        info.extendReason = reader.Get<string>("EXTEND_REASON");
                        info.batchId = reader.Get<string>("BATCH_ID");
                        info.proofCall = reader.Get<string>("PROOF_CALL");
                        info.hasilProofCall = reader.Get<string>("HASIL_PROOF_CALL");
                        info.cif = reader.Get<string>("CIF");
                        info.kontakLog = reader.Get<string>("KONTAK_LOG");
                        info.jenis_kontak = reader.Get<string>("JENIS_KONTAK");
                        info.dataType = reader.Get<string>("DATA_TYPE");
                        info.extendDate2 = reader.Get<DateTime>("EXTEND_DATE_2").ToString(FORMAT_DATE_TIME);
                        info.nipTeller = reader.Get<string>("NIPTeller");
                        info.isDownloaded = reader.Get<string>("IS_DOWNLOADED");
                        list.Add(info);
                    }
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        private DateTime DateStart(DateTime date)
        {
            return new DateTime(date.Year, date.Month, date.Day);
        }
        private DateTime DateEnd(DateTime date)
        {
            return new DateTime(date.Year, date.Month, date.Day, 23, 59, 59);
        }

        [WebMethod]
        public string GetProspectByDate(string aoCode, string unitCode,
            string startDate, string endDate, string nip,
            string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                DateTime start = DateStart(DateTime.Parse(startDate));
                DateTime end = DateEnd(DateTime.Parse(endDate));

                string sql = @"
                      SELECT PROSPECT_ID, PROSPECT_NO, FULL_NAME, KTP_NAME, KTP_NO,
                          DATE_OF_BIRTH, MARITAL_STATUS, SPOUSE_NAME, KODYA_KAB_BUSINESS, 
                          KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, ZIP_CODE_BUSINESS, ADDRESS_BUSINESS,
                          MOBILE_PHONE, OFFICE_PHONE, RESIDENTIAL_PHONE, JML_TANGGUNGAN, JENIS_USAHA, RENCANA_PLAFON,
                          PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING, REFERENSI, EKSTERNAL, PRODUCT_TYPE,
                          ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,
                          UPLOAD_BY,UPLOAD_DATE,AO_CODE,UNIT_CODE,AO_CODE,UNIT_CODE,
                          PROGRESS_STATUS, UM_DECISION, UM_DECISION_NOTE, UM_DECISION_DATE, REPUTASI_DEBITUR,
                          CO_ASSIGNED, ALTERNATE_FOR, NOT_INTERESTED_REASON, GU_CLOSE_DEAL,IS_UPLOAD,IS_DELETED,AVG_EXPENSE,KTP_ADDRESS, IS_OFFLINE_ENTRY,
                          OMZET_1, OMZET_2, OMZET_3, OMZET_4, OMZET_5, OMZET_6, OMZET_7, OMZET_8, OMZET_9, OMZET_10, OMZET_11, OMZET_12, OMZET_AVERAGE, LOAN_NUMBER,
                          CIF_NUMBER, SOURCE_CODE, PIPELINE_START_TIME, PIPELINE_FINISH_TIME, PROSPECT_START_TIME, PROSPECT_FINISH_TIME, IS_PIPELINE_KP, GU_FUP, FLAG_FREEZE, PLACE_OF_BIRTH,NIP, KARAKTER
                      FROM TBL_PROSPECT  WHERE AO_CODE = @AO_CODE AND UNIT_CODE = @UNIT_CODE AND UPDATE_DATE BETWEEN @START AND @END";

                List<MobileProspectInfo> list = new List<MobileProspectInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);
                db.AddInParameter(cmd, "@START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@END", DbType.DateTime, end);

                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        MobileProspectInfo info = CreateProspect(dr);
                        list.Add(info);
                    }

                    dr.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }


        public string str(int length)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder(length);
            for (int i = 0; i < length; i++)
                sb.Append("A");
            return sb.ToString();
        }

        [WebMethod]
        public string DumpProspectTest(int count, bool compress)
        {
            MobileResultInfo result = new MobileResultInfo();

            string date = "2000-01-01 10:10:10";

            List<MobileProspectInfo> list = new List<MobileProspectInfo>();


            for (int i = 0; i < count; i++)
            {
                MobileProspectInfo p = new MobileProspectInfo();
                p.prospectID = str(21);
                p.prospectNo = str(19);
                p.fullName = str(50);
                p.jenisUsaha = str(10);
                p.rencanaPlafon = long.MaxValue;
                p.mobilePhone = str(18);
                p.officePhone = str(12);
                p.residentialPhone = str(12);
                p.kodyaKabBusiness = str(50);
                p.kecamatanBusiness = str(50);
                p.kelurahanBusiness = str(50);
                p.prospectType = str(10);
                p.debtorType = str(10);
                p.channeling = str(10);
                p.zipCodeBusiness = str(6);
                p.addressBusiness = str(255);
                p.eksternal = str(10);
                p.referensi = str(100);
                p.avgExpense = int.MaxValue;
                p.maritalStatus = str(10);
                p.spouseName = str(50);
                p.jmlTanggungan = int.MaxValue;
                p.productCode = str(5);
                p.ktpNo = str(50);
                p.ktpName = str(50);
                p.ktpAddress = str(255);
                p.entryBy = str(9);
                p.entryDate = date;
                p.updateBy = str(9);
                p.updateDate = date;
                p.aoCode = str(5);
                p.unitCode = str(6);
                p.progressStatus = str(10);
                p.umDecision = str(1);
                p.umDecisionNote = str(255);
                p.umDecisionDate = date;
                p.reputasiDebitur = str(50);
                p.coAssigned = str(9);
                p.alternateFor = str(10);
                p.notInterestedReason = str(10);
                p.guCloseDeal = date;
                p.isUpload = str(1);
                p.uploadBy = str(9);
                p.uploadDate = date;
                p.isDeleted = str(1);
                p.isOfflineEntry = str(1);
                p.omzet = int.MaxValue;
                p.loanNumber = str(15);
                p.CIFNumber = int.MaxValue;
                p.omzet1 = long.MaxValue;
                p.omzet2 = long.MaxValue;
                p.omzet3 = long.MaxValue;
                p.omzet4 = long.MaxValue;
                p.omzet5 = long.MaxValue;
                p.omzet6 = long.MaxValue;
                p.omzet7 = long.MaxValue;
                p.omzet8 = long.MaxValue;
                p.omzet9 = long.MaxValue;
                p.omzet10 = long.MaxValue;
                p.omzet11 = long.MaxValue;
                p.omzet12 = long.MaxValue;
                p.omzetAverage = long.MaxValue;
                p.sourceCode = str(2);
                p.pipelineStartTime = date;
                p.pipelineFinishTime = date;
                p.prospectStartTime = date;
                p.prospectFinishTime = date;
                p.guFup = date;
                p.isPipelineKP = str(1);
                p.rowID = str(20);
                list.Add(p);
            }

            result.result = list.ToArray();
            string x = Serialize(result, compress);

            File.WriteAllText("D:\\prospect.json" + (compress ? ".gzip" : ""), x);

            return x;
        }

        [WebMethod]
        public string DumpProspectVisitTest(int count, bool compress)
        {
            MobileResultInfo result = new MobileResultInfo();

            string date = "2000-01-01 10:10:10";

            List<MobileProspectVisitInfo> list = new List<MobileProspectVisitInfo>();


            for (int i = 0; i < count; i++)
            {
                MobileProspectVisitInfo p = new MobileProspectVisitInfo();
                p.prospectID = str(21);
                p.dateVisitActual = date;
                p.dateVisitPlan = date;
                p.entryBy = str(9);
                p.entryDate = date;
                p.updateBy = str(9);
                p.remark = str(255);
                p.joinFieldWork = str(1);
                p.isDeleted = str(1);
                p.isUpload = str(1);
                p.resultOfVisit = str(10);
                p.uploadBy = str(9);
                p.uploadDate = date;
                p.visitNo = int.MaxValue;
                p.objectiveOfVisit = str(10);

                list.Add(p);
            }

            result.result = list.ToArray();
            string x = Serialize(result, compress);

            File.WriteAllText("D:\\prospect_visit.json" + (compress ? ".gzip" : ""), x);

            return x;
        }

        [WebMethod]
        public string DumpProspectProgressTest(int count, bool compress)
        {
            MobileResultInfo result = new MobileResultInfo();

            string date = "2000-01-01 10:10:10";

            List<MobileProspectProgressInfo> list = new List<MobileProspectProgressInfo>();

            for (int i = 0; i < count; i++)
            {
                MobileProspectProgressInfo p = new MobileProspectProgressInfo();
                p.prospectID = str(21);
                p.progressStatus = str(10);
                p.entryBy = str(9);
                p.entryDate = date;
                p.updateBy = str(9);
                p.isUpload = str(1);
                p.uploadBy = str(9);
                p.uploadDate = date;

                list.Add(p);
            }

            result.result = list.ToArray();
            string x = Serialize(result, compress);

            File.WriteAllText("D:\\prospect_progress.json" + (compress ? ".gzip" : ""), x);

            return x;
        }

        [WebMethod]
        public string DumpMaintenanceVisitTest(int count, bool compress)
        {
            MobileResultInfo result = new MobileResultInfo();

            string date = "2000-01-01 10:10:10";

            List<MobileMaintenanceVisitInfo> list = new List<MobileMaintenanceVisitInfo>();

            for (int i = 0; i < count; i++)
            {
                MobileMaintenanceVisitInfo p = new MobileMaintenanceVisitInfo();
                p.id = str(16);
                p.aoCode = str(5);
                p.unitCode = str(6);
                p.noAccount = str(15);
                p.debtorName = str(50);
                p.dateVisit = date;
                p.objectiveOfVisit = str(10);
                p.isDeleted = str(1);
                p.entryBy = str(9);
                p.entryDate = date;
                p.updateBy = str(9);
                p.isUpload = str(1);
                p.uploadBy = str(9);
                p.uploadDate = date;

                list.Add(p);
            }

            result.result = list.ToArray();
            string x = Serialize(result, compress);

            File.WriteAllText("D:\\prospect_maintenance_visit.json" + (compress ? ".gzip" : ""), x);

            return x;
        }

        [WebMethod]
        public string GetProspectUpdate(string aoCode, string unitCode,
            string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                string sql = @"
                      SELECT P.PROSPECT_ID, P.PROSPECT_NO, P.FULL_NAME, P.KTP_NAME, P.KTP_NO,
                          P.DATE_OF_BIRTH, P.MARITAL_STATUS, P.SPOUSE_NAME, P.KODYA_KAB_BUSINESS, 
                          P.KECAMATAN_BUSINESS, P.KELURAHAN_BUSINESS, P.ZIP_CODE_BUSINESS, P.ADDRESS_BUSINESS,
                          P.MOBILE_PHONE, P.OFFICE_PHONE, P.RESIDENTIAL_PHONE, P.JML_TANGGUNGAN, P.JENIS_USAHA,
                          P.RENCANA_PLAFON, P.PROSPECT_TYPE, P.DEBTOR_TYPE, P.CHANNELING, P.REFERENSI, P.EKSTERNAL,
                          P.PRODUCT_TYPE, P.ENTRY_BY, P.ENTRY_DATE, P.UPDATE_BY, P.UPDATE_DATE,
                          P.UPLOAD_BY, P.UPLOAD_DATE, P.AO_CODE, P.UNIT_CODE, P.AO_CODE, P.UNIT_CODE,
                          P.PROGRESS_STATUS, P.UM_DECISION, P.UM_DECISION_NOTE, P.UM_DECISION_DATE, P.REPUTASI_DEBITUR,
                          P.CO_ASSIGNED, P.ALTERNATE_FOR, P.NOT_INTERESTED_REASON, P.GU_CLOSE_DEAL, P.IS_UPLOAD, P.IS_DELETED, P.AVG_EXPENSE, P.KTP_ADDRESS, P.IS_OFFLINE_ENTRY,
                          P.OMZET_1, P.OMZET_2, P.OMZET_3, P.OMZET_4, P.OMZET_5, P.OMZET_6, P.OMZET_7, P.OMZET_8, P.OMZET_9, P.OMZET_10, P.OMZET_11, P.OMZET_12, P.OMZET_AVERAGE, P.LOAN_NUMBER, P.CIF_NUMBER,P.SOURCE_CODE,
                          P.PIPELINE_START_TIME, P.PIPELINE_FINISH_TIME, P.PROSPECT_START_TIME, P.PROSPECT_FINISH_TIME, P.IS_PIPELINE_KP, P.GU_FUP, P.FLAG_FREEZE,P.PLACE_OF_BIRTH, P.NIP, P.KARAKTER
                      FROM TBL_PROSPECT_UPDATE U INNER JOIN TBL_PROSPECT P ON U.PROSPECT_ID = P.PROSPECT_ID
                      WHERE P.AO_CODE = @AO_CODE AND P.UNIT_CODE = @UNIT_CODE";// AND P.UPDATE_DATE BETWEEN @START AND @END";

                List<MobileProspectInfo> list = new List<MobileProspectInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);

                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        MobileProspectInfo info = CreateProspect(dr);
                        list.Add(info);
                    }
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        /*
        [WebMethod]
        public string GetProspectByDate2(string aoCode, string unitCode, string startDate, string endDate, string nip, string password, string imei, string token)
        {
            ResultInfo result = new ResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                DateTime start = DateStart(DateTime.Parse(startDate));
                DateTime end = DateEnd(DateTime.Parse(endDate));

                string sql = @"SELECT PROSPECT_ID,PROSPECT_NO,FULL_NAME,JENIS_USAHA,RENCANA_PLAFON,
                                MOBILE_PHONE,OFFICE_PHONE,RESIDENTAL_PHONE,KODYA,KECAMATAN,KELURAHAN,
                                PROSPECT_TYPE,DEBTOR_TYPE,CHANNELING,POSTAL_CODE,[ADDRESS],EXTERNAL_TYPE,
                                REFERENCE,VISIT_DATE,AVG_EXPENSE,BIRTHDATE,MARITAL_STATUS,SPOUSE_NAME,
                                JML_TANGGUNGAN,PRODUCT,KTP_NO,KTP_NAME,KTP_ADDRESS, IS_UPLOAD,
                                ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,UPLOAD_BY,UPLOAD_DATE,
                                AO_CODE,UNIT_CODE,AO_CODE,UNIT_CODE                                
                                FROM TBL_PROSPECT_MOBILE
                                WHERE AO_CODE = @AO_CODE AND UNIT_CODE = @UNIT_CODE AND UPDATE_DATE BETWEEN @START AND @END";

                List<ProspectInfo> list = new List<ProspectInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);
                db.AddInParameter(cmd, "@START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@END", DbType.DateTime, end);

                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        ProspectInfo info = CreateProspect(dr);
                        list.Add(info);
                    }
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }*/

        [WebMethod]
        public string GetProspectVisitByDate(string aoCode, string unitCode, string startDate,
            string endDate, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                DateTime start = DateStart(DateTime.Parse(startDate));
                DateTime end = DateEnd(DateTime.Parse(endDate));

                string sql = @"SELECT V.PROSPECT_ID, V.VISIT_NO, V.OBJECTIVE_OF_VISIT, 
                                V.DATE_VISIT_PLAN, V.DATE_VISIT_ACTUAL, V.JOINT_FIELD_WORK, 
                                V.RESULT_OF_VISIT, V.REMARK, V.ENTRY_BY, V.ENTRY_DATE,
                                V.UPDATE_BY, V.UPDATE_DATE, V.UPLOAD_BY, V.UPLOAD_DATE, V.IS_UPLOAD, V.IS_DELETED
                            FROM TBL_PROSPECT_VISIT V INNER JOIN
                                TBL_PROSPECT P ON V.PROSPECT_ID = P.PROSPECT_ID
	                            WHERE P.AO_CODE = @AO_CODE AND P.UNIT_CODE = @UNIT_CODE
	                            AND V.UPDATE_DATE BETWEEN @START AND @END";

                List<MobileProspectVisitInfo> list = new List<MobileProspectVisitInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);
                db.AddInParameter(cmd, "@START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@END", DbType.DateTime, end);

                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        MobileProspectVisitInfo info = new MobileProspectVisitInfo();
                        info.prospectID = dr.Get<string>("PROSPECT_ID");
                        info.visitNo = dr.Get<byte>("VISIT_NO");
                        info.dateVisitPlan = dr.Get<DateTime>("DATE_VISIT_PLAN").ToString(FORMAT_DATE_TIME);
                        info.dateVisitActual = dr.Get<DateTime>("DATE_VISIT_ACTUAL").ToString(FORMAT_DATE_TIME);
                        info.joinFieldWork = dr.Get<string>("JOINT_FIELD_WORK");
                        info.objectiveOfVisit = dr.Get<string>("OBJECTIVE_OF_VISIT");
                        info.resultOfVisit = dr.Get<string>("RESULT_OF_VISIT");
                        info.remark = dr.Get<string>("REMARK");
                        info.entryBy = dr.Get<string>("ENTRY_BY");
                        info.entryDate = dr.Get<DateTime>("ENTRY_DATE").ToString(FORMAT_DATE_TIME);
                        info.updateBy = dr.Get<string>("UPDATE_BY");
                        info.updateDate = dr.Get<DateTime>("UPDATE_DATE").ToString(FORMAT_DATE_TIME);
                        info.uploadBy = dr.Get<string>("UPLOAD_BY");
                        info.uploadDate = dr.Get<DateTime>("UPLOAD_DATE").ToString(FORMAT_DATE_TIME);
                        info.isUpload = dr.Get<string>("IS_UPLOAD");
                        info.isDeleted = dr.Get<string>("IS_DELETED");
                        list.Add(info);
                    }

                    dr.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        private List<MobileProspectRefferalInfo> GetProspectRefferal_i(string aoCode, string unitCode)
        {
            string sql = @"SELECT REFFERAL_ID, FULL_NAME, JENIS_USAHA, ISNULL(A.MS_STD_NAME,'') AS JENIS_USAHA_DESC, 
                                    OFFICE_PHONE, ISNULL(RENCANA_PLAFON,0) AS RENCANA_PLAFON, ADDRESS_BUSINESS,
                                    UNIT_CODE_SOURCE, UNIT_CODE_DEST, AO_CODE_ORIG, ASSIGNED_TO, ASSIGNED_DATE
                                FROM TBL_PROSPECT_REFFERAL (NOLOCK) LEFT JOIN
                                    (SELECT MS_STD_ID,MS_STD_NAME FROM TBL_MS_STANDARD (NOLOCK) WHERE IS_ACTIVE = '1' AND MS_STD_GRP_ID = 14) AS A
		                            ON TBL_PROSPECT_REFFERAL.JENIS_USAHA = A.MS_STD_ID
                                WHERE RTRIM(LTRIM(TBL_PROSPECT_REFFERAL.UNIT_CODE_DEST)) = @UNIT_CODE 
                                    AND TBL_PROSPECT_REFFERAL.IS_ASSIGNED = '1' 
                                    AND (TBL_PROSPECT_REFFERAL.IS_DOWNLOADED = '0') 
                                    AND (LTRIM(RTRIM(ASSIGNED_TO)) = @AO_CODE)";

            List<MobileProspectRefferalInfo> list = new List<MobileProspectRefferalInfo>();
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    MobileProspectRefferalInfo info = new MobileProspectRefferalInfo();
                    info.refferalID = dr.SafeGet<string>("REFFERAL_ID");
                    info.fullName = dr.SafeGet<string>("FULL_NAME");
                    info.jenisUsaha = dr.SafeGet<string>("JENIS_USAHA");
                    info.jenisUsahaDesc = dr.SafeGet<string>("JENIS_USAHA_DESC");
                    info.officePhone = dr.SafeGet<string>("OFFICE_PHONE");
                    info.rencanaPlafon = Convert.ToInt64(dr.SafeGet<double>("RENCANA_PLAFON"));
                    info.addressBusiness = dr.SafeGet<string>("ADDRESS_BUSINESS");
                    info.unitCodeSource = dr.SafeGet<string>("UNIT_CODE_SOURCE");
                    info.unitCodeDest = dr.SafeGet<string>("UNIT_CODE_DEST");
                    info.aoCodeOrig = dr.SafeGet<string>("AO_CODE_ORIG");
                    info.assignedTo = dr.SafeGet<string>("ASSIGNED_TO");
                    info.assignedDate = dr.SafeGet<DateTime>("ASSIGNED_DATE").ToString(FORMAT_DATE_TIME);
                    list.Add(info);
                }

                dr.Close();
            }

            return list;
        }

        [WebMethod]
        public string GetProspectRefferal(string aoCode, string unitCode, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                result.result = GetProspectRefferal_i(aoCode, unitCode).ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result, false);
        }

        [WebMethod]
        public string GetProspectProgressByDate(string aoCode, string unitCode, string startDate,
            string endDate, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                DateTime start = DateStart(DateTime.Parse(startDate));
                DateTime end = DateEnd(DateTime.Parse(endDate));

                string sql = @"SELECT PRO.PROSPECT_ID, PRO.PROGRESS_STATUS,
                                PRO.ENTRY_BY, PRO.ENTRY_DATE, PRO.UPDATE_BY, PRO.UPDATE_DATE,
                                PRO.UPLOAD_BY, PRO.UPLOAD_DATE, PRO.IS_UPLOAD
                            FROM TBL_PROSPECT_PROGRESS PRO INNER JOIN
                                TBL_PROSPECT P ON PRO.PROSPECT_ID = P.PROSPECT_ID
	                            WHERE P.AO_CODE = @AO_CODE AND P.UNIT_CODE = @UNIT_CODE
	                            AND P.UPDATE_DATE BETWEEN @START AND @END";

                List<MobileProspectProgressInfo> list = new List<MobileProspectProgressInfo>();
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, aoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unitCode);
                db.AddInParameter(cmd, "@START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@END", DbType.DateTime, end);

                using (IDataReader dr = db.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        MobileProspectProgressInfo info = new MobileProspectProgressInfo();
                        info.prospectID = dr.Get<string>("PROSPECT_ID");
                        info.progressStatus = dr.Get<string>("PROGRESS_STATUS");
                        info.entryBy = dr.Get<string>("ENTRY_BY");
                        info.entryDate = dr.Get<DateTime>("ENTRY_DATE").ToString(FORMAT_DATE_TIME);
                        info.updateBy = dr.Get<string>("UPDATE_BY");
                        info.updateDate = dr.Get<DateTime>("UPDATE_DATE").ToString(FORMAT_DATE_TIME);
                        info.uploadBy = dr.Get<string>("UPLOAD_BY");
                        info.uploadDate = dr.Get<DateTime>("UPLOAD_DATE").ToString(FORMAT_DATE_TIME);
                        info.isUpload = dr.Get<string>("IS_UPLOAD");
                        list.Add(info);
                    }

                    dr.Close();
                }

                result.result = list.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string UploadAPK(long length, string path, string fileName, string version)
        {
            try
            {
                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"
                    UPDATE TBL_MS_MOBILE_FILE_VERSION
                       SET DATE_FILE_MODIFIED = @DATE_FILE_MODIFIED
                          ,FILE_SIZE_UPLOAD = @FILE_SIZE_UPLOAD
                          ,PATH_FILE_UPLOAD = @PATH_FILE_UPLOAD
                          ,NAME_FOLDER_UPLOAD = @NAME_FOLDER_UPLOAD
                          ,FILE_NAME_UPLOAD = @FILE_NAME_UPLOAD      
                          ,UPDATE_BY = @UPDATE_BY
                          ,UPDATE_DATE = @UPDATE_DATE      
                          ,[CHECKSUM] = @CHECKSUM
                     WHERE [VERSION] = @VERSION
                     IF @@ROWCOUNT = 0
                     INSERT INTO TBL_MS_MOBILE_FILE_VERSION
                               (DATE_FILE_UPLOAD
                               ,DATE_FILE_MODIFIED
                               ,FILE_SIZE_UPLOAD
                               ,PATH_FILE_UPLOAD
                               ,NAME_FOLDER_UPLOAD
                               ,FILE_NAME_UPLOAD
                               ,ENTRY_BY
                               ,ENTRY_DATE
                               ,UPDATE_BY
                               ,UPDATE_DATE
                               ,[VERSION]
                               ,[CHECKSUM])
                         VALUES
                               (@DATE_FILE_UPLOAD
                               ,@DATE_FILE_MODIFIED
                               ,@FILE_SIZE_UPLOAD
                               ,@PATH_FILE_UPLOAD
                               ,@NAME_FOLDER_UPLOAD
                               ,@FILE_NAME_UPLOAD
                               ,@ENTRY_BY
                               ,@ENTRY_DATE
                               ,@UPDATE_BY
                               ,@UPDATE_DATE
                               ,@VERSION
                               ,@CHECKSUM)");

                db.AddInParameter(cmd, "@DATE_FILE_UPLOAD", DbType.DateTime, DateTime.Now);
                db.AddInParameter(cmd, "@DATE_FILE_MODIFIED", DbType.DateTime, DateTime.Now);
                db.AddInParameter(cmd, "@FILE_SIZE_UPLOAD", DbType.Int64, length);
                db.AddInParameter(cmd, "@PATH_FILE_UPLOAD", DbType.String, path);
                db.AddInParameter(cmd, "@NAME_FOLDER_UPLOAD", DbType.String, "APK");
                db.AddInParameter(cmd, "@FILE_NAME_UPLOAD", DbType.String, fileName);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, "system");
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, DateTime.Now);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, "system");
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, DateTime.Now);
                db.AddInParameter(cmd, "@VERSION", DbType.String, version);
                db.AddInParameter(cmd, "@CHECKSUM", DbType.Int64, DateTime.Now.Ticks);
                db.ExecuteNonQuery(cmd);

                return null;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        [WebMethod]
        public string GetUpdatePath(string version)
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_MOBILE_FILE_VERSION WHERE [VERSION] = @VERSION");
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);

            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    return System.IO.Path.Combine(
                        reader.Get<string>("PATH_FILE_UPLOAD"),
                        reader.Get<string>("FILE_NAME_UPLOAD"));
                }
                return null;
            }
        }

        [WebMethod]
        public MobileVersionInfo CheckForUpdateSPADA(string version, string nip, string password)
        {
            if (!IsLDAPUser(nip, password))
                throw new Exception("Authenticate failed!");

            MobileVersionInfo info = GetLatestVersion(version);

            return info;
        }

        [WebMethod]
        public MobileVersionInfo CheckForUpdateSPADAByUnit(string version, string nip, string password, string unit)
        {
            if (!IsLDAPUser(nip, password))
                throw new Exception("Authenticate failed!");

            MobileVersionInfo info = GetLatestVersionByUnit(version, unit);

            return info;
        }


        public MobileVersionInfo GetLatestVersion(string ver)
        {
            MobileVersionInfo info = GetLatestVersion();

            if (info != null && info.version.CompareTo(ver) > 0)
                return info;

            return null;
        }

        [WebMethod]
        public MobileVersionInfo GetLatestVersion()
        {
            string sql = @"SELECT TOP 1 * FROM TBL_MS_MOBILE_FILE_VERSION ORDER BY VERSION DESC";

            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr.Read())
                {
                    MobileVersionInfo info = new MobileVersionInfo();
                    info.version = dr.Get<string>("VERSION");
                    info.size = GetFileSizeDesc(dr.Get<long>("FILE_SIZE_UPLOAD"));
                    info.checksum = dr.Get<long>("CHECKSUM");

                    return info;
                }
            }

            return null;
        }

        [WebMethod]
        public MobileVersionInfo GetLatestVersionByUnit(string version, string unit)
        {
            Database db = DBConn.CreateDatabase();

            DbCommand cmd = db.GetStoredProcCommand(@"WS_GetSpamobileUpdate");

            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr.Read())
                {
                    MobileVersionInfo info = new MobileVersionInfo();
                    info.version = dr.Get<string>("VERSION");
                    info.size = GetFileSizeDesc(dr.Get<long>("FILE_SIZE_UPLOAD"));
                    info.checksum = dr.Get<long>("CHECKSUM");

                    return info;
                }
            }

            return null;
        }

        [WebMethod]
        public string CheckForUpdate(string version, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                MobileVersionInfo info = GetLatestVersion(version);
                if (info != null)
                    result.result = info;
                else
                    throw new Exception("Application is up to date");

            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string CheckForUpdateByUnit(string version, string nip, string password, string imei, string token, string unit)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);

                MobileVersionInfo info = GetLatestVersionByUnit(version, unit);
                if (info != null)
                    result.result = info;
                else
                    throw new Exception("Application is up to date");

            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        private MobileProspectInfo CreateProspect(IDataReader dr)
        {
            MobileProspectInfo info = new MobileProspectInfo();
            info.prospectID = dr.Get<string>("PROSPECT_ID");
            info.prospectNo = dr.Get<string>("PROSPECT_NO");
            info.fullName = dr.SafeGet<string>("FULL_NAME");
            info.ktpName = dr.SafeGet<string>("KTP_NAME");
            info.ktpNo = dr.SafeGet<string>("KTP_NO");
            info.dateOfBirth = dr.SafeGet<DateTime>("DATE_OF_BIRTH").ToString(FORMAT_DATE_TIME);
            info.maritalStatus = dr.SafeGet<string>("MARITAL_STATUS");
            info.spouseName = dr.SafeGet<string>("SPOUSE_NAME");
            info.kodyaKabBusiness = dr.SafeGet<string>("KODYA_KAB_BUSINESS");
            info.kecamatanBusiness = dr.SafeGet<string>("KECAMATAN_BUSINESS");
            info.kelurahanBusiness = dr.SafeGet<string>("KELURAHAN_BUSINESS");
            info.zipCodeBusiness = dr.SafeGet<string>("ZIP_CODE_BUSINESS");
            info.addressBusiness = dr.SafeGet<string>("ADDRESS_BUSINESS");
            info.mobilePhone = dr.SafeGet<string>("MOBILE_PHONE");
            info.officePhone = dr.SafeGet<string>("OFFICE_PHONE");
            info.residentialPhone = dr.SafeGet<string>("RESIDENTIAL_PHONE");
            info.jmlTanggungan = dr.SafeGet<short>("JML_TANGGUNGAN");
            info.jenisUsaha = dr.SafeGet<string>("JENIS_USAHA");
            info.rencanaPlafon = Convert.ToInt64(dr.SafeGet<double>("RENCANA_PLAFON"));
            info.prospectType = dr.SafeGet<string>("PROSPECT_TYPE");
            info.debtorType = dr.SafeGet<string>("DEBTOR_TYPE");
            info.channeling = dr.SafeGet<string>("CHANNELING");
            info.referensi = dr.SafeGet<string>("REFERENSI");
            info.eksternal = dr.SafeGet<string>("EKSTERNAL");
            info.productCode = dr.SafeGet<short>("PRODUCT_TYPE").ToString();
            info.avgExpense = Convert.ToInt32(dr.SafeGet<double>("AVG_EXPENSE"));
            info.progressStatus = dr.SafeGet<string>("PROGRESS_STATUS");
            info.entryBy = dr.SafeGet<string>("ENTRY_BY");
            info.entryDate = dr.SafeGet<DateTime>("ENTRY_DATE").ToString(FORMAT_DATE_TIME);
            info.updateBy = dr.SafeGet<string>("UPDATE_BY");
            info.updateDate = dr.SafeGet<DateTime>("UPDATE_DATE").ToString(FORMAT_DATE_TIME);
            info.aoCode = dr.SafeGet<string>("AO_CODE");
            info.unitCode = dr.SafeGet<string>("UNIT_CODE");
            info.umDecision = dr.SafeGet<string>("UM_DECISION");
            info.umDecisionNote = dr.SafeGet<string>("UM_DECISION_NOTE");
            info.umDecisionDate = dr.SafeGet<DateTime>("UM_DECISION_DATE").ToString(FORMAT_DATE_TIME);
            info.coAssigned = dr.SafeGet<string>("CO_ASSIGNED");
            info.alternateFor = dr.SafeGet<string>("ALTERNATE_FOR");
            info.reputasiDebitur = dr.SafeGet<string>("REPUTASI_DEBITUR");
            info.notInterestedReason = dr.SafeGet<string>("NOT_INTERESTED_REASON");
            info.guCloseDeal = dr.SafeGet<DateTime>("GU_CLOSE_DEAL").ToString(FORMAT_DATE_TIME);
            info.uploadBy = dr.SafeGet<string>("UPLOAD_BY");
            info.uploadDate = dr.SafeGet<DateTime>("UPLOAD_DATE").ToString(FORMAT_DATE_TIME);
            info.isUpload = dr.SafeGet<string>("IS_UPLOAD");
            info.isDeleted = dr.SafeGet<string>("IS_DELETED");
            info.ktpAddress = dr.SafeGet<string>("KTP_ADDRESS");
            info.isOfflineEntry = dr.SafeGet<string>("IS_OFFLINE_ENTRY");
            info.loanNumber = dr.SafeGet<string>("LOAN_NUMBER");
            info.CIFNumber = dr.SafeGet<int>("CIF_NUMBER");
            info.omzet1 = Convert.ToInt64(dr["OMZET_1"]);
            info.omzet2 = Convert.ToInt64(dr["OMZET_2"]);
            info.omzet3 = Convert.ToInt64(dr["OMZET_3"]);
            info.omzet4 = Convert.ToInt64(dr["OMZET_4"]);
            info.omzet5 = Convert.ToInt64(dr["OMZET_5"]);
            info.omzet6 = Convert.ToInt64(dr["OMZET_6"]);
            info.omzet7 = Convert.ToInt64(dr["OMZET_7"]);
            info.omzet8 = Convert.ToInt64(dr["OMZET_8"]);
            info.omzet9 = Convert.ToInt64(dr["OMZET_9"]);
            info.omzet10 = Convert.ToInt64(dr["OMZET_10"]);
            info.omzet11 = Convert.ToInt64(dr["OMZET_11"]);
            info.omzet12 = Convert.ToInt64(dr["OMZET_12"]);
            info.omzetAverage = Convert.ToInt64(dr["OMZET_AVERAGE"]);
            info.sourceCode = dr.SafeGet<string>("SOURCE_CODE");
            info.pipelineStartTime = dr.SafeGet<DateTime>("PIPELINE_START_TIME").ToString(FORMAT_DATE_TIME);
            info.pipelineFinishTime = dr.SafeGet<DateTime>("PIPELINE_FINISH_TIME").ToString(FORMAT_DATE_TIME);
            info.prospectStartTime = dr.SafeGet<DateTime>("PROSPECT_START_TIME").ToString(FORMAT_DATE_TIME);
            info.prospectFinishTime = dr.SafeGet<DateTime>("PROSPECT_FINISH_TIME").ToString(FORMAT_DATE_TIME);
            info.isPipelineKP = dr.SafeGet<string>("IS_PIPELINE_KP");
            info.guFup = dr.SafeGet<DateTime>("GU_FUP").ToString(FORMAT_DATE_TIME);
            info.flagFreeze = dr.SafeGet<short>("FLAG_FREEZE");
            info.placeOfBirth = dr.SafeGet<string>("PLACE_OF_BIRTH");
            info.nip = dr.SafeGet<string>("NIP");
            info.karakter = dr.SafeGet<string>("KARAKTER");
            return info;
        }

        /*
        private ProspectInfo CreateProspect2(IDataReader dr)
        {
            ProspectInfo info = new ProspectInfo();
            info.prospectID = dr.Get<string>("PROSPECT_ID");
            info.prospectNo = dr.SafeGet<string>("PROSPECT_NO");
            info.fullName = dr.Get<string>("FULL_NAME");
            info.ktpName = dr.SafeGet<string>("KTP_NAME");
            info.ktpAddress = dr.SafeGet<string>("KTP_ADDRESS");
            info.ktpNo = dr.SafeGet<string>("KTP_NO");
            info.dateOfBirth = dr.SafeGet<DateTime>("BIRTHDATE").ToString(FORMAT_DATE_ONLY);
            info.maritalStatus = dr.SafeGet<string>("MARITAL_STATUS");
            info.spouseName = dr.SafeGet<string>("SPOUSE_NAME");
            info.kodyaKabBusiness = dr.Get<string>("KODYA");
            info.kecamatanBusiness = dr.Get<string>("KECAMATAN");
            info.kelurahanBusiness = dr.Get<string>("KELURAHAN");
            info.zipCodeBusiness = dr.Get<string>("POSTAL_CODE");
            info.addressBusiness = dr.Get<string>("ADDRESS");
            info.mobilePhone = dr.Get<string>("MOBILE_PHONE");
            info.officePhone = dr.Get<string>("OFFICE_PHONE");
            info.residentialPhone = dr.Get<string>("RESIDENTAL_PHONE");
            info.jmlTanggungan = dr.SafeGet<int>("JML_TANGGUNGAN");
            info.jenisUsaha = dr.Get<string>("JENIS_USAHA");
            info.rencanaPlafon = dr.Get<long>("RENCANA_PLAFON");
            info.prospectType = dr.Get<string>("PROSPECT_TYPE");
            info.debtorType = dr.Get<string>("DEBTOR_TYPE");
            info.channeling = dr.Get<string>("CHANNELING");
            info.referensi = dr.Get<string>("REFERENCE");
            info.eksternal = dr.Get<string>("EXTERNAL_TYPE");
            info.productCode = dr.SafeGet<string>("PRODUCT");
            info.avgExpense = Convert.ToInt32(dr.SafeGet<double>("AVG_EXPENSE"));            
            info.entryBy = dr.Get<string>("ENTRY_BY");
            info.entryDate = dr.Get<DateTime>("ENTRY_DATE").ToString(FORMAT_DATE_TIME);
            info.updateBy = dr.Get<string>("UPDATE_BY");
            info.updateDate = dr.Get<DateTime>("UPDATE_DATE").ToString(FORMAT_DATE_TIME);
            info.aoCode = dr.Get<string>("AO_CODE");
            info.unitCode = dr.Get<string>("UNIT_CODE");
            return info;
        }*/

        [WebMethod]
        public string UpdateProspectRefferal(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                string[] ids = data.Split(',');

                string sql = @"UPDATE TBL_PROSPECT_REFFERAL SET IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE WHERE REFFERAL_ID = @REFFERAL_ID";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, nip);
                db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.String, DateTime.Now);
                db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String);

                List<string> idList = new List<string>();

                foreach (string id in ids)
                {

                    SetParam(db, cmd, "@REFFERAL_ID", id);

                    if (db.ExecuteNonQuery(cmd) > 0) idList.Add(id);
                }

                result.result = idList.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string UpdateMaintenanceVisitKP(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                string[] ids = data.Split(',');

                string sql = @"UPDATE TBL_MAINTENANCE_VISIT_SPAWEB SET IS_DOWNLOADED = '1' WHERE VISIT_ID = @VISIT_ID";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@VISIT_ID", DbType.String);

                List<string> idList = new List<string>();

                foreach (string id in ids)
                {

                    SetParam(db, cmd, "@VISIT_ID", id);

                    if (db.ExecuteNonQuery(cmd) > 0) idList.Add(id);
                }

                result.result = idList.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string SendRefferalFunding(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                MobileSendRefferalFundingInfo info = Deserialize<MobileSendRefferalFundingInfo>(data);

                string sql = @"INSERT INTO TBL_PROSPECT_FUNDING_REFFERAL
                               VALUES (
                               @REF_ID,
                               @FULLNAME,
                               @PRODUCT_CODE,
                               @OFFICE_PHONE,
                               @RENCANA_SETORAN_AWAL,
                               @ADDRESS_BUSINESS,
                               @UNIT_CODE_SOURCE,
                               @UNIT_CODE_DEST,
                               @AO_CODE_ORIGIN,
                               @RES_100,
                               @ENTRY_BY,
                               @ENTRY_DATE,
                               @UPDATE_BY,
                               @UPDATE_DATE,
                               @UPLOAD_BY,
                               @UPLOAD_DATE,
                               @IS_DOWNLOADED,
                               @DOWNLOAD_BY,
                               @DOWNLOAD_DATE,
                               @IS_ASSIGNED,
                               @ASSIGNED_TO,
                               @ASSIGNED_DATE)";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@REF_ID", DbType.String, info.refID);
                db.AddInParameter(cmd, "@FULLNAME", DbType.String, info.fullName);
                db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, info.productCode);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, info.officePhone);
                db.AddInParameter(cmd, "@RENCANA_SETORAN_AWAL", DbType.String, info.rencanaSetoran);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, info.addressBussiness);
                db.AddInParameter(cmd, "@UNIT_CODE_SOURCE", DbType.String, info.unitCodeSource);
                db.AddInParameter(cmd, "@UNIT_CODE_DEST", DbType.String, info.unitCodeDest);
                db.AddInParameter(cmd, "@AO_CODE_ORIGIN", DbType.String, info.aoCodeOrigin);
                db.AddInParameter(cmd, "@RES_100", DbType.String, info.res100);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, info.entryBy);                
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(info.entryDate));
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, info.updateBy);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(info.updateDate));
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, info.uploadBy);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, Convert.ToDateTime(info.uploadDate));
                db.AddInParameter(cmd, "@IS_DOWNLOADED", DbType.String, info.isDownloaded);
                db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, info.downloadBy);
                db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, null);
                db.AddInParameter(cmd, "@IS_ASSIGNED", DbType.String, info.isAssigned);
                db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, info.assignTo);
                db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, info.assignDate);

                db.ExecuteNonQuery(cmd);
                result.result = info.refID;
            }

            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string SendRefferalLending(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                MobileSendRefferalLendingInfo info = Deserialize<MobileSendRefferalLendingInfo>(data);

                string sql = @"INSERT INTO TBL_PROSPECT_REFFERAL
                               VALUES (
                               @REF_ID,
                               @FULLNAME,
                               @JENIS_USAHA,
                               @OFFICE_PHONE,
                               @RENCANA_PLAFON,
                               @ADDRESS_BUSINESS,
                               @UNIT_CODE_SOURCE,
                               @UNIT_CODE_DEST,
                               @AO_CODE_ORIGIN,
                               @ENTRY_BY,
                               @ENTRY_DATE,
                               @UPDATE_BY,
                               @UPDATE_DATE,
                               @UPLOAD_BY,
                               @UPLOAD_DATE,
                               @IS_DOWNLOADED,
                               @DOWNLOAD_BY,
                               @DOWNLOAD_DATE,
                               @IS_ASSIGNED,
                               @ASSIGNED_TO,
                               @ASSIGNED_DATE)";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@REF_ID", DbType.String, info.refID);
                db.AddInParameter(cmd, "@FULLNAME", DbType.String, info.fullName);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, info.jenisUsaha);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, info.officePhone);
                db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.String, info.rencanaPlafon);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, info.addressBussiness);
                db.AddInParameter(cmd, "@UNIT_CODE_SOURCE", DbType.String, info.unitCodeSource);
                db.AddInParameter(cmd, "@UNIT_CODE_DEST", DbType.String, info.unitCodeDest);
                db.AddInParameter(cmd, "@AO_CODE_ORIGIN", DbType.String, info.aoCodeOrigin);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, info.entryBy);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(info.entryDate));
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, info.updateBy);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(info.updateDate));
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, info.uploadBy);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, Convert.ToDateTime(info.uploadDate));
                db.AddInParameter(cmd, "@IS_DOWNLOADED", DbType.String, info.isDownloaded);
                db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, info.downloadBy);
                db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, null);
                db.AddInParameter(cmd, "@IS_ASSIGNED", DbType.String, info.isAssigned);
                db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, info.assignTo);
                db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, info.assignDate);

                db.ExecuteNonQuery(cmd);
                result.result = info.refID;
            }

            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string UploadMaintenanceVisit(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                MobileMaintenanceVisitInfo[] array = Deserialize<MobileMaintenanceVisitInfo[]>(data);

                string sql = @"UPDATE TBL_MAINTENANCE_VISIT_TMP
                               SET 
                                  AO_CODE = @AO_CODE
                                  ,UNIT_CODE = @UNIT_CODE
                                  ,DATE_VISIT = @DATE_VISIT
                                  ,NO_ACCOUNT = @NO_ACCOUNT
                                  ,DEBTOR_NAME = @DEBTOR_NAME
                                  ,OBJECTIVE_OF_VISIT = @OBJECTIVE_OF_VISIT                                  
                                  ,UPDATE_BY = @UPDATE_BY
                                  ,UPDATE_DATE = @UPDATE_DATE
                                  ,UPLOAD_BY = @UPLOAD_BY
                                  ,UPLOAD_DATE = @UPLOAD_DATE
                                  ,IS_UPLOAD = @IS_UPLOAD
                                  ,IS_DELETED = @IS_DELETED
                                  ,RECORD_ID = @RECORD_ID
                                  ,EXPIRY_DATE = @EXPIRY_DATE
                                  ,EXTEND_DATE = @EXTEND_DATE
                                  ,EXTEND_REASON = @EXTEND_REASON
                                  ,BATCH_ID = @BATCH_ID
                                  ,PROOF_CALL = @PROOF_CALL
                                  ,HASIL_PROOF_CALL = @HASIL_PROOF_CALL
                                  ,CIF = @CIF
                                  ,KONTAK_LOG = @KONTAK_LOG
                                  ,JENIS_KONTAK = @JENIS_KONTAK
                                  ,DATA_TYPE = @DATA_TYPE
                                  ,EXTEND_DATE_2 = @EXTEND_DATE_2
                                  ,NIPTeller = @NIPTeller
                             WHERE VISIT_ID = @VISIT_ID
                             IF @@ROWCOUNT = 0
                             INSERT INTO TBL_MAINTENANCE_VISIT_TMP
                            (VISIT_ID,PROSPECT_ID,AO_CODE,UNIT_CODE,DATE_VISIT
                            ,NO_ACCOUNT,DEBTOR_NAME,OBJECTIVE_OF_VISIT
                            ,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE
                            ,UPLOAD_BY,UPLOAD_DATE,IS_UPLOAD,IS_DELETED,RECORD_ID
                            ,EXPIRY_DATE,EXTEND_DATE,EXTEND_REASON,BATCH_ID,PROOF_CALL
                            ,HASIL_PROOF_CALL,CIF,KONTAK_LOG,JENIS_KONTAK,DATA_TYPE
                            ,EXTEND_DATE_2,NIPTeller)
                            VALUES
                            (@VISIT_ID,@PROSPECT_ID,@AO_CODE,@UNIT_CODE,@DATE_VISIT
                            ,@NO_ACCOUNT,@DEBTOR_NAME,@OBJECTIVE_OF_VISIT
                            ,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE
                            ,@UPLOAD_BY,@UPLOAD_DATE,@IS_UPLOAD,@IS_DELETED
                            ,@RECORD_ID,@EXPIRY_DATE,@EXTEND_DATE,@EXTEND_REASON
                            ,@BATCH_ID,@PROOF_CALL,@HASIL_PROOF_CALL,@CIF
                            ,@KONTAK_LOG,@JENIS_KONTAK,@DATA_TYPE,@EXTEND_DATE_2,@NIPTeller)";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@VISIT_ID", DbType.String);
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String);
                db.AddInParameter(cmd, "@DATE_VISIT", DbType.DateTime);
                db.AddInParameter(cmd, "@NO_ACCOUNT", DbType.String);
                db.AddInParameter(cmd, "@DEBTOR_NAME", DbType.String);
                db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.String);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String);
                db.AddInParameter(cmd, "@IS_DELETED", DbType.String);
                db.AddInParameter(cmd, "@RECORD_ID", DbType.String);
                db.AddInParameter(cmd, "@EXPIRY_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@EXTEND_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@EXTEND_REASON", DbType.String);
                db.AddInParameter(cmd, "@BATCH_ID", DbType.String);
                db.AddInParameter(cmd, "@PROOF_CALL", DbType.String);
                db.AddInParameter(cmd, "@HASIL_PROOF_CALL", DbType.String);
                db.AddInParameter(cmd, "@CIF", DbType.String);
                db.AddInParameter(cmd, "@KONTAK_LOG", DbType.String);
                db.AddInParameter(cmd, "@JENIS_KONTAK", DbType.String);
                db.AddInParameter(cmd, "@DATA_TYPE", DbType.String);
                db.AddInParameter(cmd, "@EXTEND_DATE_2", DbType.DateTime);
                db.AddInParameter(cmd, "@NIPTeller", DbType.String);

                db.SetParameterValue(cmd, "@PROSPECT_ID", "");
                db.SetParameterValue(cmd, "@UPLOAD_BY", nip);
                db.SetParameterValue(cmd, "@UPLOAD_DATE", date);
                db.SetParameterValue(cmd, "@IS_UPLOAD", "1");

                List<string> idList = new List<string>();

                foreach (MobileMaintenanceVisitInfo info in array)
                {

                    SetParam(db, cmd, "@VISIT_ID", info.id);
                    SetParam(db, cmd, "@AO_CODE", info.aoCode);
                    SetParam(db, cmd, "@UNIT_CODE", info.unitCode);
                    SetParam(db, cmd, "@DATE_VISIT", CDate(info.dateVisit));
                    SetParam(db, cmd, "@NO_ACCOUNT", info.noAccount);
                    SetParam(db, cmd, "@DEBTOR_NAME", info.debtorName);
                    SetParam(db, cmd, "@OBJECTIVE_OF_VISIT", info.objectiveOfVisit);
                    SetParam(db, cmd, "@ENTRY_BY", info.entryBy);
                    SetParam(db, cmd, "@ENTRY_DATE", CDate(info.entryDate));
                    SetParam(db, cmd, "@UPDATE_BY", info.updateBy);
                    SetParam(db, cmd, "@UPDATE_DATE", CDate(info.updateDate));
                    SetParam(db, cmd, "@IS_DELETED", info.isDeleted);
                    SetParam(db, cmd, "@RECORD_ID", info.recordId);
                    SetParam(db, cmd, "@EXPIRY_DATE",CDate(info.expiryDate));
                    SetParam(db, cmd, "@EXTEND_DATE",CDate(info.extendDate));
                    SetParam(db, cmd, "@EXTEND_REASON", info.extendReason);
                    SetParam(db, cmd, "@BATCH_ID", info.batchId);
                    SetParam(db, cmd, "@PROOF_CALL", info.proofCall);
                    SetParam(db, cmd, "@HASIL_PROOF_CALL", info.hasilProofCall);
                    SetParam(db, cmd, "@CIF", info.cif);
                    SetParam(db, cmd, "@KONTAK_LOG", info.kontakLog);
                    SetParam(db, cmd, "@JENIS_KONTAK", info.jenis_kontak);
                    SetParam(db, cmd, "@DATA_TYPE", info.dataType);
                    SetParam(db, cmd, "@EXTEND_DATE_2",CDate(info.extendDate2));
                    SetParam(db, cmd, "@NIPTeller", info.nipTeller);

                    if (db.ExecuteNonQuery(cmd) > 0) idList.Add(info.id);
                }

                result.result = idList.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        private void MoveToTemp(string id)
        {

        }

        [WebMethod]
        public string UploadProspect(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                MobileProspectInfo[] array = Deserialize<MobileProspectInfo[]>(data);

                string sql = @"UPDATE TBL_PROSPECT_TMP  
                              SET FULL_NAME = @FULL_NAME
                              ,PROSPECT_NO = @PROSPECT_NO
                              ,JENIS_USAHA = @JENIS_USAHA
                              ,RENCANA_PLAFON = @RENCANA_PLAFON
                              ,MOBILE_PHONE = @MOBILE_PHONE
                              ,OFFICE_PHONE = @OFFICE_PHONE
                              ,RESIDENTIAL_PHONE = @RESIDENTIAL_PHONE
                              ,KODYA_KAB_BUSINESS = @KODYA_KAB_BUSINESS
                              ,KECAMATAN_BUSINESS = @KECAMATAN_BUSINESS
                              ,KELURAHAN_BUSINESS = @KELURAHAN_BUSINESS
                              ,PROSPECT_TYPE = @PROSPECT_TYPE
                              ,DEBTOR_TYPE = @DEBTOR_TYPE
                              ,CHANNELING = @CHANNELING
                              ,ZIP_CODE_BUSINESS = @ZIP_CODE_BUSINESS
                              ,ADDRESS_BUSINESS = @ADDRESS_BUSINESS
                              ,EKSTERNAL = @EKSTERNAL
                              ,REFERENSI = @REFERENSI                              
                              ,AVG_EXPENSE = @AVG_EXPENSE
                              ,DATE_OF_BIRTH = @DATE_OF_BIRTH
                              ,MARITAL_STATUS = @MARITAL_STATUS
                              ,SPOUSE_NAME = @SPOUSE_NAME
                              ,JML_TANGGUNGAN = @JML_TANGGUNGAN
                              --,PRODUCT_CODE = @PRODUCT_CODE
                              ,PRODUCT_TYPE = @PRODUCT_CODE
                              ,KTP_NO = @KTP_NO
                              ,KTP_NAME = @KTP_NAME                              
                              ,ENTRY_BY = @ENTRY_BY
                              ,ENTRY_DATE = @ENTRY_DATE
                              ,UPDATE_BY = @UPDATE_BY
                              ,UPDATE_DATE = @UPDATE_DATE
                              ,UPLOAD_BY = @UPLOAD_BY
                              ,UPLOAD_DATE = @UPLOAD_DATE
                              ,AO_CODE = @AO_CODE
                              ,UNIT_CODE = @UNIT_CODE
                              ,PROGRESS_STATUS = @PROGRESS_STATUS
                              ,UM_DECISION = @UM_DECISION
                              ,UM_DECISION_NOTE = @UM_DECISION_NOTE
                              ,UM_DECISION_DATE = @UM_DECISION_DATE
                              ,REPUTASI_DEBITUR = @REPUTASI_DEBITUR
                              ,CO_ASSIGNED = @CO_ASSIGNED
                              ,ALTERNATE_FOR = @ALTERNATE_FOR
                              ,NOT_INTERESTED_REASON = @NOT_INTERESTED_REASON
                              ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL
                              ,IS_DELETED = @IS_DELETED
                              ,IS_UPLOAD = @IS_UPLOAD
                              ,KTP_ADDRESS = @KTP_ADDRESS
                              ,IS_OFFLINE_ENTRY = @IS_OFFLINE_ENTRY
                              ,OMZET_1 = @OMZET_1
                              ,OMZET_2 = @OMZET_2
                              ,OMZET_3 = @OMZET_3
                              ,OMZET_4 = @OMZET_4
                              ,OMZET_5 = @OMZET_5
                              ,OMZET_6 = @OMZET_6
                              ,OMZET_7 = @OMZET_7
                              ,OMZET_8 = @OMZET_8
                              ,OMZET_9 = @OMZET_9
                              ,OMZET_10 = @OMZET_10
                              ,OMZET_11 = @OMZET_11
                              ,OMZET_12 = @OMZET_12
                              ,OMZET_AVERAGE = @OMZET_AVERAGE                              
                              ,LOAN_NUMBER = @LOAN_NUMBER
                              ,CIF_NUMBER = @CIF_NUMBER
                              ,SOURCE_CODE = @SOURCE_CODE
                              ,PIPELINE_START_TIME = @PIPELINE_START_TIME
                              ,PIPELINE_FINISH_TIME = @PIPELINE_FINISH_TIME
                              ,PROSPECT_START_TIME = @PROSPECT_START_TIME
                              ,PROSPECT_FINISH_TIME = @PROSPECT_FINISH_TIME
                              ,IS_PIPELINE_KP = @IS_PIPELINE_KP
                              ,GU_FUP = @GU_FUP
                              ,FLAG_FREEZE = @FLAG_FREEZE
                              ,PLACE_OF_BIRTH = @PLACE_OF_BIRTH                    
                            ,LAST_UPDATE_FROM = '1'
                            ,FUNDING_LENDING = @FUNDING_LENDING
                            ,NIP = @NIP
                            ,KARAKTER = @KARAKTER
                         WHERE  PROSPECT_ID = @PROSPECT_ID
                         IF @@ROWCOUNT = 0
                        INSERT INTO TBL_PROSPECT_TMP
                           (PROSPECT_ID
                           ,PROSPECT_NO
                           ,FULL_NAME
                           ,JENIS_USAHA
                           ,RENCANA_PLAFON
                           ,MOBILE_PHONE
                           ,OFFICE_PHONE
                           ,RESIDENTIAL_PHONE
                           ,KODYA_KAB_BUSINESS
                           ,KECAMATAN_BUSINESS
                           ,KELURAHAN_BUSINESS
                           ,PROSPECT_TYPE
                           ,DEBTOR_TYPE
                           ,CHANNELING
                           ,ZIP_CODE_BUSINESS
                           ,ADDRESS_BUSINESS
                           ,EKSTERNAL
                           ,REFERENSI                                                     
                           ,AVG_EXPENSE
                           ,DATE_OF_BIRTH
                           ,MARITAL_STATUS
                           ,SPOUSE_NAME
                           ,JML_TANGGUNGAN
                           ,PRODUCT_CODE
                           ,KTP_NO
                           ,KTP_NAME                           
                           ,ENTRY_BY
                           ,ENTRY_DATE
                           ,UPDATE_BY
                           ,UPDATE_DATE
                           ,UPLOAD_BY
                           ,UPLOAD_DATE
                           ,AO_CODE
                           ,UNIT_CODE
                           ,PROGRESS_STATUS
                           ,UM_DECISION
                           ,UM_DECISION_NOTE
                           ,UM_DECISION_DATE
                           ,REPUTASI_DEBITUR
                           ,CO_ASSIGNED
                           ,ALTERNATE_FOR
                           ,NOT_INTERESTED_REASON
                           ,GU_CLOSE_DEAL
                           ,IS_DELETED
                           ,IS_UPLOAD
                           ,KTP_ADDRESS
                           ,IS_OFFLINE_ENTRY
                           ,OMZET_1
                           ,OMZET_2
                           ,OMZET_3
                           ,OMZET_4
                           ,OMZET_5
                           ,OMZET_6
                           ,OMZET_7
                           ,OMZET_8
                           ,OMZET_9
                           ,OMZET_10
                           ,OMZET_11
                           ,OMZET_12
                           ,OMZET_AVERAGE                           
                           ,LOAN_NUMBER
                           ,CIF_NUMBER
                           ,SOURCE_CODE                            
                           ,PLACE_OF_BIRTH
                           ,MOTHER_NAME
                           ,MALE_FEMALE           
                           ,SPOUSE_DATE_OF_BIRTH     
                           ,NPWP_NO                 
                           ,RESIDENTIAL_STATUS                     
                           ,OFFICE_STATUS           
                           ,YEAR_OF_STAY           
                           ,LAMA_USAHA
                           ,OMSET_PER_HARI
                           ,ADA_REK_BANK           
                           ,SALES_CHANNEL           
                           ,FUNDING_LENDING
                           ,RESTRUCT_COMPONENT          
                           ,CYCLE_STATUS
                           ,INSTALLMENT
                           ,FAP_DATE
                           ,NO_OF_CALLS
                           ,IS_INTERESTED                      
                           ,KET_KENDALA
                           ,SCM_NOTE
                           ,PRODUCT_TYPE           
                           ,KARAKTER           
                           ,RIWAYAT_DSP
                           ,TAKSIRAN_PLAFON
                           ,TAKSIRAN_USAHA
                           ,IS_DOCUMENT_COMPLETED           
                           ,CURRENT_OS
                           ,CURRENT_INSTALLMENT
                           ,OFFER_PLAFON
                           ,OFFER_INSTALLMENT
                           ,OFFER_INTEREST
                           ,OFFER_TENOR
                           ,OFFER_FRESH_MONEY
                           ,[PRIORITY]           
                           ,BI_GOOD_UNTIL
                           ,IS_BI_CHECKING                               
                           ,GU_FUP
                           ,IS_DEDUP           
                           ,IS_PIPELINE_KP           
                           ,RES_1
                           ,RES_2
                           ,RES_3
                           ,RES_4
                           ,RES_5
                           ,RES_6
                           ,RES_7
                           ,RES_8
                           ,RES_9
                           ,RES_10
                           ,RES_11
                           ,RES_12
                           ,RES_13
                           ,RES_14
                           ,RES_15
                           ,RES_16
                           ,RES_17
                           ,RES_18
                           ,RES_19
                           ,RES_20
                           ,RES_21
                           ,RES_22
                           ,RES_23
                           ,RES_24
                           ,RES_25
                           ,RES_26
                           ,RES_27
                           ,RES_28
                           ,RES_29
                           ,RES_30
                           ,RES_31
                           ,RES_32
                           ,RES_33
                           ,RES_34
                           ,RES_35
                           ,RES_36
                           ,RES_37
                           ,RES_38
                           ,RES_39
                           ,RES_40
                           ,RES_41
                           ,RES_42
                           ,RES_43
                           ,RES_44
                           ,RES_45
                           ,RES_46
                           ,RES_47
                           ,RES_48
                           ,RES_49
                           ,RES_50
                           ,RES_51
                           ,RES_52
                           ,RES_53
                           ,RES_54
                           ,RES_55
                           ,RES_56
                           ,RES_57
                           ,RES_58
                           ,RES_59
                           ,RES_60
                           ,RES_61
                           ,RES_62
                           ,RES_63
                           ,RES_64
                           ,RES_65
                           ,RES_66
                           ,RES_67
                           ,RES_68
                           ,RES_69
                           ,RES_70
                           ,RES_71
                           ,RES_72
                           ,RES_73
                           ,RES_74
                           ,RES_75
                           ,RES_76
                           ,RES_77
                           ,RES_78
                           ,RES_79
                           ,RES_80
                           ,RES_81
                           ,RES_82
                           ,RES_83
                           ,RES_84
                           ,RES_85
                           ,RES_86
                           ,RES_87
                           ,RES_88
                           ,RES_89
                           ,RES_90
                           ,RES_91
                           ,RES_92
                           ,RES_93
                           ,RES_94
                           ,RES_95
                           ,RES_96
                           ,RES_97
                           ,RES_98
                           ,RES_99
                           ,RES_100
                           ,NIP
                           ,OLD_NIP
                           ,OLD_AO_CODE                 
                           ,FLAG_FREEZE
                           ,NOT_INTERESTED_NOTE
                           ,PIPELINE_START_TIME
                           ,PIPELINE_FINISH_TIME
                           ,PROSPECT_START_TIME
                           ,PROSPECT_FINISH_TIME
                           ,LAST_UPDATE_FROM)
                     VALUES
                           (@PROSPECT_ID
                           ,@PROSPECT_NO
                           ,@FULL_NAME
                           ,@JENIS_USAHA
                           ,@RENCANA_PLAFON
                           ,@MOBILE_PHONE
                           ,@OFFICE_PHONE
                           ,@RESIDENTIAL_PHONE
                           ,@KODYA_KAB_BUSINESS
                           ,@KECAMATAN_BUSINESS
                           ,@KELURAHAN_BUSINESS
                           ,@PROSPECT_TYPE
                           ,@DEBTOR_TYPE
                           ,@CHANNELING
                           ,@ZIP_CODE_BUSINESS
                           ,@ADDRESS_BUSINESS
                           ,@EKSTERNAL
                           ,@REFERENSI                                                     
                           ,@AVG_EXPENSE
                           ,@DATE_OF_BIRTH
                           ,@MARITAL_STATUS
                           ,@SPOUSE_NAME
                           ,@JML_TANGGUNGAN
                           --,@PRODUCT_CODE
                            ,''
                           ,@KTP_NO
                           ,@KTP_NAME                           
                           ,@ENTRY_BY
                           ,@ENTRY_DATE
                           ,@UPDATE_BY
                           ,@UPDATE_DATE
                           ,@UPLOAD_BY
                           ,@UPLOAD_DATE
                           ,@AO_CODE
                           ,@UNIT_CODE
                           ,@PROGRESS_STATUS
                           ,@UM_DECISION
                           ,@UM_DECISION_NOTE
                           ,@UM_DECISION_DATE
                           ,@REPUTASI_DEBITUR
                           ,@CO_ASSIGNED
                           ,@ALTERNATE_FOR
                           ,@NOT_INTERESTED_REASON
                           ,@GU_CLOSE_DEAL
                           ,@IS_DELETED
                           ,@IS_UPLOAD
                           ,@KTP_ADDRESS
                           ,@IS_OFFLINE_ENTRY                                                      
                           ,@OMZET_1
                           ,@OMZET_2
                           ,@OMZET_3
                           ,@OMZET_4
                           ,@OMZET_5
                           ,@OMZET_6
                           ,@OMZET_7
                           ,@OMZET_8
                           ,@OMZET_9
                           ,@OMZET_10
                           ,@OMZET_11
                           ,@OMZET_12
                           ,@OMZET_AVERAGE
                           ,@LOAN_NUMBER
                           ,@CIF_NUMBER
                           ,@SOURCE_CODE
                           ,@PLACE_OF_BIRTH--<PLACE_OF_BIRTH, varchar(50),>
                           ,''--<MOTHER_NAME, varchar(50),>
                           ,''--<MALE_FEMALE, varchar(10),>           
                           ,'1900-01-01'--<SPOUSE_DATE_OF_BIRTH, datetime,>
                           ,''--<NPWP_NO, varchar(50),>           
                           ,''--<RESIDENTIAL_STATUS, varchar(10),>           
                           ,''--<OFFICE_STATUS, varchar(10),>           
                           ,0--<YEAR_OF_STAY, smallint,>           
                           ,''--<LAMA_USAHA, varchar(10),>
                           ,0--<OMSET_PER_HARI, int,>
                           ,''--<ADA_REK_BANK, char(1),>           
                           ,''--<SALES_CHANNEL, varchar(10),>           
                           ,@FUNDING_LENDING--<FUNDING_LENDING, char(1),>
                           ,''--<RESTRUCT_COMPONENT, varchar(10),>           
                           ,''--<CYCLE_STATUS, varchar(10),>
                           ,0--<INSTALLMENT, float,>
                           ,'2009-01-01'--<FAP_DATE, datetime,>
                           ,0--<NO_OF_CALLS, smallint,>
                           ,''--<IS_INTERESTED, char(1),>           
                           ,''--<KET_KENDALA, varchar(255),>
                           ,''--<SCM_NOTE, varchar(255),>  
                           ,@PRODUCT_CODE--<PRODUCT_TYPE>                     
                           ,@KARAKTER--<KARAKTER, char(1),>           
                           ,''--<RIWAYAT_DSP, varchar(50),>
                           ,0--<TAKSIRAN_PLAFON, float,>
                           ,''--<TAKSIRAN_USAHA, varchar(50),>
                           ,''--<IS_DOCUMENT_COMPLETED, char(1),>           
                           ,0--<CURRENT_OS, float,>
                           ,0--<CURRENT_INSTALLMENT, float,>
                           ,0--<OFFER_PLAFON, float,>
                           ,0--<OFFER_INSTALLMENT, float,>
                           ,''--<OFFER_INTEREST, varchar(5),>
                           ,0--<OFFER_TENOR, smallint,>
                           ,0--<OFFER_FRESH_MONEY, float,>
                           ,''--<PRIORITY, varchar(20),>           
                           ,'2009-01-01'--<BI_GOOD_UNTIL, datetime,>
                           ,''--<IS_BI_CHECKING, char(1),>                      
                           ,@GU_FUP
                           ,'0'--<IS_DEDUP, char(1),>           
                           ,@IS_PIPELINE_KP
                           ,''--<RES_1, varchar(10),>
                           ,''--<RES_2, varchar(10),>
                           ,''--<RES_3, varchar(10),>
                           ,''--<RES_4, varchar(10),>
                           ,''--<RES_5, varchar(10),>
                           ,''--<RES_6, varchar(10),>
                           ,''--<RES_7, varchar(10),>
                           ,''--<RES_8, varchar(10),>
                           ,''--<RES_9, varchar(10),>
                           ,''--<RES_10, varchar(10),>
                           ,''--<RES_11, varchar(10),>
                           ,''--<RES_12, varchar(10),>
                           ,''--<RES_13, varchar(10),>
                           ,''--<RES_14, varchar(10),>
                           ,''--<RES_15, varchar(10),>
                           ,''--<RES_16, varchar(10),>
                           ,''--<RES_17, varchar(10),>
                           ,''--<RES_18, varchar(10),>
                           ,''--<RES_19, varchar(10),>
                           ,''--<RES_20, varchar(10),>
                           ,''--<RES_21, varchar(10),>
                           ,''--<RES_22, varchar(10),>
                           ,''--<RES_23, varchar(10),>
                           ,''--<RES_24, varchar(10),>
                           ,''--<RES_25, varchar(10),>
                           ,'2009-01-01'--<RES_26, datetime,>
                           ,'2009-01-01'--<RES_27, datetime,>
                           ,'2009-01-01'--<RES_28, datetime,>
                           ,'2009-01-01'--<RES_29, datetime,>
                           ,'2009-01-01'--<RES_30, datetime,>
                           ,'2009-01-01'--<RES_31, datetime,>
                           ,'2009-01-01'--<RES_32, datetime,>
                           ,'2009-01-01'--<RES_33, datetime,>
                           ,'2009-01-01'--<RES_34, datetime,>
                           ,'2009-01-01'--<RES_35, datetime,>
                           ,'2009-01-01'--<RES_36, datetime,>
                           ,'2009-01-01'--<RES_37, datetime,>
                           ,'2009-01-01'--<RES_38, datetime,>
                           ,'2009-01-01'--<RES_39, datetime,>
                           ,'2009-01-01'--<RES_40, datetime,>
                           ,'2009-01-01'--<RES_41, datetime,>
                           ,'2009-01-01'--<RES_42, datetime,>
                           ,'2009-01-01'--<RES_43, datetime,>
                           ,'2009-01-01'--<RES_44, datetime,>
                           ,'2009-01-01'--<RES_45, datetime,>
                           ,'2009-01-01'--<RES_46, datetime,>
                           ,'2009-01-01'--<RES_47, datetime,>
                           ,'2009-01-01'--<RES_48, datetime,>
                           ,'2009-01-01'--<RES_49, datetime,>
                           ,'2009-01-01'--<RES_50, datetime,>
                           ,0--<RES_51, float,>
                           ,0--<RES_52, float,>
                           ,0--<RES_53, float,>
                           ,0--<RES_54, float,>
                           ,0--<RES_55, float,>
                           ,0--<RES_56, float,>
                           ,0--<RES_57, float,>
                           ,0--<RES_58, float,>
                           ,0--<RES_59, float,>
                           ,0--<RES_60, float,>
                           ,0--<RES_61, float,>
                           ,0--<RES_62, float,>
                           ,0--<RES_63, float,>
                           ,0--<RES_64, float,>
                           ,0--<RES_65, float,>
                           ,0--<RES_66, float,>
                           ,0--<RES_67, float,>
                           ,0--<RES_68, float,>
                           ,0--<RES_69, float,>
                           ,0--<RES_70, float,>
                           ,0--<RES_71, float,>
                           ,0--<RES_72, float,>
                           ,0--<RES_73, float,>
                           ,0--<RES_74, float,>
                           ,0--<RES_75, float,>
                           ,''--<RES_76, varchar(255),>
                           ,''--<RES_77, varchar(255),>
                           ,''--<RES_78, varchar(255),>
                           ,''--<RES_79, varchar(255),>
                           ,''--<RES_80, varchar(255),>
                           ,''--<RES_81, varchar(255),>
                           ,''--<RES_82, varchar(255),>
                           ,''--<RES_83, varchar(255),>
                           ,''--<RES_84, varchar(255),>
                           ,''--<RES_85, varchar(255),>
                           ,''--<RES_86, varchar(255),>
                           ,''--<RES_87, varchar(255),>
                           ,''--<RES_88, varchar(255),>
                           ,''--<RES_89, varchar(255),>
                           ,''--<RES_90, varchar(255),>
                           ,''--<RES_91, varchar(255),>
                           ,''--<RES_92, varchar(255),>
                           ,''--<RES_93, varchar(255),>
                           ,''--<RES_94, varchar(255),>
                           ,''--<RES_95, varchar(255),>
                           ,''--<RES_96, varchar(255),>
                           ,''--<RES_97, varchar(255),>
                           ,''--<RES_98, varchar(255),>
                           ,''--<RES_99, varchar(255),>
                           ,''--<RES_100, varchar(255),>
                           ,@NIP--<NIP, varchar(9),>
                           ,''--<OLD_NIP, varchar(9),>
                           ,''--<OLD_AO_CODE, char(5),>                      
                           ,@FLAG_FREEZE--<FLAG_FREEZE, smallint,>
                           ,''--NOT_INTERESTED_NOTE
                           ,@PIPELINE_START_TIME
                           ,@PIPELINE_FINISH_TIME
                           ,@PROSPECT_START_TIME
                           ,@PROSPECT_FINISH_TIME
                           ,'1'--LASTUPDATEFROM
)";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String);
                db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String);
                db.AddInParameter(cmd, "@FULL_NAME", DbType.String);
                db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String);
                db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Int64);
                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String);
                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String);
                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String);
                db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String);
                db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String);
                db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String);
                db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String);
                db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String);
                db.AddInParameter(cmd, "@CHANNELING", DbType.String);
                db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String);
                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String);
                db.AddInParameter(cmd, "@EKSTERNAL", DbType.String);
                db.AddInParameter(cmd, "@REFERENSI", DbType.String);
                db.AddInParameter(cmd, "@AVG_EXPENSE", DbType.Double);
                db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime);
                db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String);
                db.AddInParameter(cmd, "@SPOUSE_NAME", DbType.String);
                db.AddInParameter(cmd, "@JML_TANGGUNGAN", DbType.Int32);
                db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String);
                db.AddInParameter(cmd, "@KTP_NO", DbType.String);
                db.AddInParameter(cmd, "@KTP_NAME", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String);
                db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String);
                db.AddInParameter(cmd, "@UM_DECISION", DbType.String);
                db.AddInParameter(cmd, "@UM_DECISION_NOTE", DbType.String);
                db.AddInParameter(cmd, "@UM_DECISION_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@REPUTASI_DEBITUR", DbType.String);
                db.AddInParameter(cmd, "@CO_ASSIGNED", DbType.String);
                db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String);
                db.AddInParameter(cmd, "@NOT_INTERESTED_REASON", DbType.String);
                db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.DateTime);
                db.AddInParameter(cmd, "@IS_DELETED", DbType.String);
                db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String);
                db.AddInParameter(cmd, "@KTP_ADDRESS", DbType.String);
                db.AddInParameter(cmd, "@IS_OFFLINE_ENTRY", DbType.String);
                db.AddInParameter(cmd, "@OMZET_1", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_2", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_3", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_4", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_5", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_6", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_7", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_8", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_9", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_10", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_11", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_12", DbType.Double);
                db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String);
                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32);
                db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String);
                db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime);
                db.AddInParameter(cmd, "@PIPELINE_FINISH_TIME", DbType.DateTime);
                db.AddInParameter(cmd, "@PROSPECT_START_TIME", DbType.DateTime);
                db.AddInParameter(cmd, "@PROSPECT_FINISH_TIME", DbType.DateTime);
                db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String);
                db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime);
                db.AddInParameter(cmd, "@FLAG_FREEZE", DbType.Int16);
                db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String);
                db.AddInParameter(cmd, "@FUNDING_LENDING", DbType.String);
                db.AddInParameter(cmd, "@NIP", DbType.String);
                db.AddInParameter(cmd, "@KARAKTER", DbType.String);

                List<string> idList = new List<string>();

                foreach (MobileProspectInfo info in array)
                {
                    SetParam(db, cmd, "@PROSPECT_ID", info.prospectID);
                    SetParam(db, cmd, "@PROSPECT_NO", info.prospectNo);
                    SetParam(db, cmd, "@FULL_NAME", info.fullName);
                    SetParam(db, cmd, "@JENIS_USAHA", info.jenisUsaha);
                    SetParam(db, cmd, "@RENCANA_PLAFON", info.rencanaPlafon);
                    SetParam(db, cmd, "@MOBILE_PHONE", info.mobilePhone);
                    SetParam(db, cmd, "@OFFICE_PHONE", info.officePhone);
                    SetParam(db, cmd, "@RESIDENTIAL_PHONE", info.residentialPhone);
                    SetParam(db, cmd, "@KODYA_KAB_BUSINESS", info.kodyaKabBusiness);
                    SetParam(db, cmd, "@KECAMATAN_BUSINESS", info.kecamatanBusiness);
                    SetParam(db, cmd, "@KELURAHAN_BUSINESS", info.kelurahanBusiness);
                    SetParam(db, cmd, "@PROSPECT_TYPE", info.prospectType);
                    SetParam(db, cmd, "@DEBTOR_TYPE", info.debtorType);
                    SetParam(db, cmd, "@CHANNELING", info.channeling);
                    SetParam(db, cmd, "@ZIP_CODE_BUSINESS", info.zipCodeBusiness);
                    SetParam(db, cmd, "@ADDRESS_BUSINESS", info.addressBusiness);
                    SetParam(db, cmd, "@EKSTERNAL", info.eksternal);
                    SetParam(db, cmd, "@REFERENSI", info.referensi);
                    SetParam(db, cmd, "@AVG_EXPENSE", info.avgExpense);
                    SetParam(db, cmd, "@DATE_OF_BIRTH", CDate(info.dateOfBirth));
                    SetParam(db, cmd, "@MARITAL_STATUS", info.maritalStatus);
                    SetParam(db, cmd, "@SPOUSE_NAME", info.spouseName);
                    SetParam(db, cmd, "@JML_TANGGUNGAN", info.jmlTanggungan);
                    SetParam(db, cmd, "@PRODUCT_CODE", info.productCode);
                    SetParam(db, cmd, "@KTP_NO", info.ktpNo);
                    SetParam(db, cmd, "@KTP_NAME", info.ktpName);
                    SetParam(db, cmd, "@ENTRY_BY", info.entryBy);
                    SetParam(db, cmd, "@ENTRY_DATE", CDate(info.entryDate));
                    SetParam(db, cmd, "@UPDATE_BY", info.updateBy);
                    SetParam(db, cmd, "@UPDATE_DATE", CDate(info.updateDate));
                    SetParam(db, cmd, "@AO_CODE", info.aoCode);
                    SetParam(db, cmd, "@UNIT_CODE", info.unitCode);
                    SetParam(db, cmd, "@UPLOAD_BY", nip);
                    SetParam(db, cmd, "@UPLOAD_DATE", date);
                    SetParam(db, cmd, "@IS_UPLOAD", "1");
                    SetParam(db, cmd, "@PROGRESS_STATUS", info.progressStatus);
                    SetParam(db, cmd, "@UM_DECISION", info.umDecision);
                    SetParam(db, cmd, "@UM_DECISION_NOTE", info.umDecisionNote);
                    SetParam(db, cmd, "@UM_DECISION_DATE", CDate(info.umDecisionDate));
                    SetParam(db, cmd, "@REPUTASI_DEBITUR", info.reputasiDebitur);
                    SetParam(db, cmd, "@CO_ASSIGNED", info.coAssigned);
                    SetParam(db, cmd, "@ALTERNATE_FOR", info.alternateFor);
                    SetParam(db, cmd, "@NOT_INTERESTED_REASON", info.notInterestedReason);
                    SetParam(db, cmd, "@GU_CLOSE_DEAL", CDate(info.guCloseDeal));
                    SetParam(db, cmd, "@IS_DELETED", info.isDeleted);
                    SetParam(db, cmd, "@KTP_ADDRESS", info.ktpAddress);
                    SetParam(db, cmd, "@IS_OFFLINE_ENTRY", info.isOfflineEntry);

                    SetParam(db, cmd, "@CIF_NUMBER", info.CIFNumber);
                    SetParam(db, cmd, "@LOAN_NUMBER", info.loanNumber);
                    SetParam(db, cmd, "@OMZET_1", info.omzet1);
                    SetParam(db, cmd, "@OMZET_2", info.omzet2);
                    SetParam(db, cmd, "@OMZET_3", info.omzet3);
                    SetParam(db, cmd, "@OMZET_4", info.omzet4);
                    SetParam(db, cmd, "@OMZET_5", info.omzet5);
                    SetParam(db, cmd, "@OMZET_6", info.omzet6);
                    SetParam(db, cmd, "@OMZET_7", info.omzet7);
                    SetParam(db, cmd, "@OMZET_8", info.omzet8);
                    SetParam(db, cmd, "@OMZET_9", info.omzet9);
                    SetParam(db, cmd, "@OMZET_10", info.omzet10);
                    SetParam(db, cmd, "@OMZET_11", info.omzet11);
                    SetParam(db, cmd, "@OMZET_12", info.omzet12);
                    SetParam(db, cmd, "@OMZET_AVERAGE", info.omzetAverage);

                    SetParam(db, cmd, "@SOURCE_CODE", info.sourceCode);

                    SetParam(db, cmd, "@PIPELINE_START_TIME", CDate(info.pipelineStartTime));
                    SetParam(db, cmd, "@PIPELINE_FINISH_TIME", CDate(info.pipelineFinishTime));
                    SetParam(db, cmd, "@PROSPECT_START_TIME", CDate(info.prospectStartTime));
                    SetParam(db, cmd, "@PROSPECT_FINISH_TIME", CDate(info.prospectFinishTime));

                    SetParam(db, cmd, "@IS_PIPELINE_KP", info.isPipelineKP);
                    SetParam(db, cmd, "@GU_FUP", CDate(info.guFup));
                    SetParam(db, cmd, "@FLAG_FREEZE", info.flagFreeze);
                    SetParam(db, cmd, "@PLACE_OF_BIRTH", info.placeOfBirth);
                    SetParam(db, cmd, "@KARAKTER", info.karakter);

                    string productType = "";
                    if (!String.IsNullOrEmpty(info.productCode))
                    {
                        int productCode;
                        if (!int.TryParse(info.productCode, out productCode))
                            productCode = 0;

                        productType = GetProductType(productCode);
                    }

                    SetParam(db, cmd, "@FUNDING_LENDING", productType);

                    SetParam(db, cmd, "@NIP", info.nip);

                    if (db.ExecuteNonQuery(cmd) > 0) idList.Add(info.prospectID);
                }

                result.result = idList.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string UploadProspectVisit(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                MobileProspectVisitInfo[] array = Deserialize<MobileProspectVisitInfo[]>(data);

                string sql = @"UPDATE TBL_PROSPECT_VISIT_TMP
                               SET 
                                  OBJECTIVE_OF_VISIT = @OBJECTIVE_OF_VISIT
                                  ,DATE_VISIT_PLAN = @DATE_VISIT_PLAN
                                  ,DATE_VISIT_ACTUAL = @DATE_VISIT_ACTUAL
                                  ,JOINT_FIELD_WORK = @JOINT_FIELD_WORK
                                  ,RESULT_OF_VISIT = @RESULT_OF_VISIT
                                  ,REMARK = @REMARK
                                  ,ENTRY_BY = @ENTRY_BY
                                  ,ENTRY_DATE = @ENTRY_DATE
                                  ,UPDATE_BY = @UPDATE_BY
                                  ,UPDATE_DATE = @UPDATE_DATE
                                  ,UPLOAD_BY = @UPLOAD_BY
                                  ,UPLOAD_DATE = @UPLOAD_DATE
                                  ,IS_UPLOAD = @IS_UPLOAD
                                  ,IS_DELETED = @IS_DELETED
                             WHERE PROSPECT_ID = @PROSPECT_ID
                                  AND VISIT_NO = @VISIT_NO
                         IF @@ROWCOUNT = 0
                        INSERT INTO TBL_PROSPECT_VISIT_TMP
                               (PROSPECT_ID
                               ,VISIT_NO
                               ,OBJECTIVE_OF_VISIT
                               ,DATE_VISIT_PLAN
                               ,DATE_VISIT_ACTUAL
                               ,JOINT_FIELD_WORK
                               ,RESULT_OF_VISIT
                               ,REMARK
                               ,ENTRY_BY
                               ,ENTRY_DATE
                               ,UPDATE_BY
                               ,UPDATE_DATE
                               ,UPLOAD_BY
                               ,UPLOAD_DATE
                               ,IS_UPLOAD
                               ,IS_DELETED)
                         VALUES
                               (@PROSPECT_ID
                               ,@VISIT_NO
                               ,@OBJECTIVE_OF_VISIT
                               ,@DATE_VISIT_PLAN
                               ,@DATE_VISIT_ACTUAL
                               ,@JOINT_FIELD_WORK
                               ,@RESULT_OF_VISIT
                               ,@REMARK
                               ,@ENTRY_BY
                               ,@ENTRY_DATE
                               ,@UPDATE_BY
                               ,@UPDATE_DATE
                               ,@UPLOAD_BY
                               ,@UPLOAD_DATE
                               ,@IS_UPLOAD
                               ,@IS_DELETED)";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String);
                db.AddInParameter(cmd, "@VISIT_NO", DbType.Byte);
                db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String);
                db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime);
                db.AddInParameter(cmd, "@DATE_VISIT_ACTUAL", DbType.DateTime);
                db.AddInParameter(cmd, "@JOINT_FIELD_WORK", DbType.String);
                db.AddInParameter(cmd, "@RESULT_OF_VISIT", DbType.String);
                db.AddInParameter(cmd, "@REMARK", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String);
                db.AddInParameter(cmd, "@IS_DELETED", DbType.String);

                db.SetParameterValue(cmd, "@UPLOAD_BY", nip);
                db.SetParameterValue(cmd, "@UPLOAD_DATE", date);
                db.SetParameterValue(cmd, "@IS_UPLOAD", "1");

                List<string> idList = new List<string>();

                foreach (MobileProspectVisitInfo info in array)
                {

                    SetParam(db, cmd, "@PROSPECT_ID", info.prospectID);
                    SetParam(db, cmd, "@VISIT_NO", info.visitNo);
                    SetParam(db, cmd, "@OBJECTIVE_OF_VISIT", info.objectiveOfVisit);
                    SetParam(db, cmd, "@DATE_VISIT_PLAN", CDate(info.dateVisitPlan));
                    SetParam(db, cmd, "@DATE_VISIT_ACTUAL", CDate(info.dateVisitActual));
                    SetParam(db, cmd, "@JOINT_FIELD_WORK", info.joinFieldWork);
                    SetParam(db, cmd, "@RESULT_OF_VISIT", info.resultOfVisit);
                    SetParam(db, cmd, "@REMARK", info.remark);
                    SetParam(db, cmd, "@ENTRY_BY", info.entryBy);
                    SetParam(db, cmd, "@ENTRY_DATE", CDate(info.entryDate));
                    SetParam(db, cmd, "@UPDATE_BY", info.updateBy);
                    SetParam(db, cmd, "@UPDATE_DATE", CDate(info.updateDate));
                    SetParam(db, cmd, "@IS_DELETED", info.isDeleted);

                    if (db.ExecuteNonQuery(cmd) > 0) idList.Add(info.prospectID + "~" + info.visitNo);
                }

                result.result = idList.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        [WebMethod]
        public string DelProspectUpdate(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                string[] ids = Convert.ToBase64String(Decompress(Convert.FromBase64String(data))).Split(',');

                string sql = @"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String);

                List<string> idList = new List<string>();

                foreach (string id in ids)
                {
                    SetParam(db, cmd, "@PROSPECT_ID", id);
                    if (db.ExecuteNonQuery(cmd) > 0) idList.Add(id);
                }

                result.result = idList.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        private void SetParam(Database db, DbCommand cmd, string paramName, object value)
        {
            db.SetParameterValue(cmd, paramName, value);
        }

        private void SetParam(Database db, DbCommand cmd, string paramName, string value)
        {
            db.SetParameterValue(cmd, paramName, value ?? "");
        }

        private void SetParam(Database db, DbCommand cmd, string paramName, DateTime? date)
        {
            DateTime d = date ?? MIN_DATE;
            db.SetParameterValue(cmd, paramName, d == DateTime.MinValue ? MIN_DATE : d);
        }

        private void SetParam(Database db, DbCommand cmd, string paramName, DateTime date)
        {
            db.SetParameterValue(cmd, paramName, date == DateTime.MinValue ? MIN_DATE : date);
        }

        [WebMethod]
        public string UploadProspectProgress(string data, string nip, string password, string imei, string token)
        {
            MobileResultInfo result = new MobileResultInfo();

            try
            {
                Authenticate(nip, password, imei, token);
                MobileProspectProgressInfo[] array = Deserialize<MobileProspectProgressInfo[]>(data);

                string sql = @"UPDATE TBL_PROSPECT_PROGRESS_TMP
                               SET                                   
                                  UPDATE_BY = @UPDATE_BY
                                  ,UPDATE_DATE = @UPDATE_DATE
                                  ,UPLOAD_BY = @UPLOAD_BY
                                  ,UPLOAD_DATE = @UPLOAD_DATE
                                  ,IS_UPLOAD = @IS_UPLOAD                                  
                             WHERE PROSPECT_ID = @PROSPECT_ID
                                  AND PROGRESS_STATUS = @PROGRESS_STATUS
                         IF @@ROWCOUNT = 0
                        INSERT INTO TBL_PROSPECT_PROGRESS_TMP
                               (PROSPECT_ID, PROGRESS_STATUS                               
                               ,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE
                               ,UPLOAD_BY,UPLOAD_DATE,IS_UPLOAD)
                         VALUES
                               (@PROSPECT_ID,@PROGRESS_STATUS                               
                               ,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE
                               ,@UPLOAD_BY,@UPLOAD_DATE,@IS_UPLOAD)";

                Database db = CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DateTime date = DateTime.Now;

                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String);
                db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String);
                db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime);
                db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String);

                db.SetParameterValue(cmd, "@UPLOAD_BY", nip);
                db.SetParameterValue(cmd, "@UPLOAD_DATE", date);
                db.SetParameterValue(cmd, "@IS_UPLOAD", "1");

                List<string> idList = new List<string>();

                foreach (MobileProspectProgressInfo info in array)
                {

                    SetParam(db, cmd, "@PROSPECT_ID", info.prospectID);
                    SetParam(db, cmd, "@PROGRESS_STATUS", info.progressStatus);
                    SetParam(db, cmd, "@ENTRY_BY", info.entryBy);
                    SetParam(db, cmd, "@ENTRY_DATE", CDate(info.entryDate));
                    SetParam(db, cmd, "@UPDATE_BY", info.updateBy);
                    SetParam(db, cmd, "@UPDATE_DATE", CDate(info.updateDate));

                    if (db.ExecuteNonQuery(cmd) > 0) idList.Add(info.prospectID + "~" + info.progressStatus);
                }

                result.result = idList.ToArray();
            }
            catch (Exception ex)
            {
                result.error = ex.Message;
            }

            return Serialize(result);
        }

        public DateTime GetServerDate()
        {
            return DateTime.Now;
        }

        private DateTime GetProcessingDate()
        {
            DateTime processDate = DateTime.Now;
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 ISNULL(PROCESS_DATE, GETDATE()) PROCESS_DATE FROM TBL_SYS_PARAMS  ORDER BY UPDATE_DATE DESC");
            return (DateTime)db.ExecuteScalar(cmd);
        }

        private DateTime GetPurgingPeriod()
        {
            Database db = CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT TOP 1 PURGING_PERIOD FROM TBL_SYS_PARAMS ORDER BY UPDATE_DATE DESC");
            return (DateTime)db.ExecuteScalar(cmd);
        }

        public static string Serialize<T>(T t)
        {
            return Serialize<T>(t, true);
        }

        public static string Serialize<T>(T t, bool compress)
        {
            DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(T));
            using (MemoryStream ms = new MemoryStream())
            {
                ser.WriteObject(ms, t);
                byte[] bytes = ms.ToArray();
                if (compress)
                    bytes = Compress(bytes);
                return Convert.ToBase64String(bytes); //System.Text.ASCIIEncoding.Default.GetString(bytes);
            }
        }

        private static byte[] Compress(byte[] bytes)
        {
            using (MemoryStream memory = new MemoryStream())
            {
                using (GZipStream gzip = new GZipStream(memory,
                    CompressionMode.Compress, true))
                {
                    gzip.Write(bytes, 0, bytes.Length);
                }
                return memory.ToArray();
            }
        }

        public static T Deserialize<T>(string json)
        {
            byte[] bytes = Decompress(Convert.FromBase64String(json));
            DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(T));
            using (MemoryStream ms = new MemoryStream(bytes))
            {
                T result = (T)ser.ReadObject(ms);
                return result;
            }
        }

        private static byte[] Decompress(byte[] bytes)
        {
            using (GZipStream stream = new GZipStream(new MemoryStream(bytes),
                CompressionMode.Decompress))
            {
                const int size = 4096;
                byte[] buffer = new byte[size];
                using (MemoryStream memory = new MemoryStream())
                {
                    int count = 0;
                    do
                    {
                        count = stream.Read(buffer, 0, size);
                        if (count > 0)
                        {
                            memory.Write(buffer, 0, count);
                        }
                    }
                    while (count > 0);
                    return memory.ToArray();
                }
            }
        }

        public static Database CreateDatabase()
        {
            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();

            string DBServer = ConfigurationManager.AppSettings["db.server"];
            string DBName = ConfigurationManager.AppSettings["db.name"];
            string DBUser = ConfigurationManager.AppSettings["db.user"];
            string DBPass = Encryption.Decrypt(ConfigurationManager.AppSettings["db.pwd"]);

            string cn = String.Format("Data Source = {0}; Initial Catalog = {1}; User Id = {2}; Password = {3};",
                DBServer, DBName, DBUser, DBPass);

            DbProviderFactory provider = DbProviderFactories.GetFactory("System.Data.SqlClient");
            return new GenericDatabase(cn, provider);
        }

        public static Database CreateDatabase(string dbName, string userName, string password)
        {
            Shuba.Framework.Encryption Encryption = new Shuba.Framework.Encryption();

            string DBServer = ConfigurationManager.AppSettings["db.server"];
            string cn = String.Format("Data Source = {0}; Initial Catalog = {1}; User Id = {2}; Password = {3};",
                DBServer, dbName, userName, password);

            DbProviderFactory provider = DbProviderFactories.GetFactory("System.Data.SqlClient");
            return new GenericDatabase(cn, provider);
        }
    }

    public static class IDataReaderExtension
    {
        public static T Get<T>(this IDataReader reader, string name)
        {
            return (T)reader[name];
        }

        public static T Get<T>(this IDataReader reader, int index)
        {
            return (T)reader[index];
        }

        public static T SafeGet<T>(this IDataReader reader, int index)
        {
            if (reader.IsDBNull(index))
                return default(T);

            return (T)reader[index];
        }

        public static T SafeGet<T>(this IDataReader reader, string name)
        {
            if (reader.IsDBNull(reader.GetOrdinal(name)))
                return default(T);

            return (T)reader[name];
        }
    }


    [KnownType(typeof(MobileAreaInfo[]))]
    [KnownType(typeof(MobileStandardInfo[]))]
    [KnownType(typeof(MobileProductInfo[]))]
    [KnownType(typeof(MobileUserInfo))]
    [KnownType(typeof(MobileProspectInfo[]))]
    [KnownType(typeof(MobileChannelInfo[]))]
    [KnownType(typeof(MobileProspectVisitInfo[]))]
    [KnownType(typeof(MobileProspectProgressInfo[]))]
    [KnownType(typeof(MobileMaintenanceVisitInfo[]))]
    [KnownType(typeof(MobileMaintenanceVisitKpInfo[]))]
    [KnownType(typeof(string[]))]
    [KnownType(typeof(MobileVersionInfo))]
    [KnownType(typeof(MobileHolidayInfo[]))]
    [KnownType(typeof(MobileUpdateTableInfo[]))]
    [KnownType(typeof(MobileExternalMappingInfo[]))]
    [KnownType(typeof(MobileFlagFreezeInfo[]))]
    [KnownType(typeof(MobileProspectRefferalInfo[]))]
    [KnownType(typeof(MobileBranchInfo[]))]
    [KnownType(typeof(MobileSendRefferalFundingInfo[]))]
    [KnownType(typeof(MobileSendRefferalLendingInfo[]))]

    [DataContract]
    public class MobileResultInfo
    {
        [DataMember]
        public string error;

        [DataMember]
        public object result;
    }

    [DataContract]
    public class MobileStandardInfo
    {
        [DataMember]
        public string group;
        [DataMember]
        public string value;
        [DataMember]
        public string text;
        [DataMember]
        public string type;
        [DataMember]
        public string isActive;
    }

    [DataContract]
    public class MobileUserInfo
    {
        [DataMember]
        public string id;
        [DataMember]
        public string nip;
        [DataMember]
        public string userName;
        [DataMember]
        public string unitCode;
        [DataMember]
        public string aoCode;
        [DataMember]
        public string encryptionKey;
        [DataMember]
        public string imei;
        [DataMember]
        public string emailAddress;
        [DataMember]
        public string token;
        [DataMember]
        public string sourceCode;
        [DataMember]
        public string aoType;


        //CONFIG
        [DataMember]
        public String processDate;
        [DataMember]
        public int guFup;
        [DataMember]
        public int guCloseDeal;
        [DataMember]
        public int purgingPeriod;
        [DataMember]
        public int timeOut;

        public bool isLogin;
        public DateTime lastLogin;
    }

    [DataContract]
    public class MobileProductInfo
    {
        [DataMember]
        public string id;
        [DataMember]
        public string name;
        [DataMember]
        public string productType;
        [DataMember]
        public string isActive;
    }

    [DataContract]
    public class MobileVersionInfo
    {
        [DataMember]
        public string version;
        [DataMember]
        public string size;
        [DataMember]
        public long checksum;
    }

    [DataContract]
    public class MobileChannelInfo
    {
        [DataMember]
        public string id;
        [DataMember]
        public string name;
        [DataMember]
        public string isActive;
    }

    [DataContract]
    public class MobileAreaInfo
    {
        [DataMember]
        public string zip;
        [DataMember]
        public string kodya;
        [DataMember]
        public string kecamatan;
        [DataMember]
        public string kelurahan;
    }

    [DataContract]
    public class MobileProspectInfo
    {
        [DataMember]
        public string prospectID;
        [DataMember]
        public string prospectNo;
        [DataMember]
        public string fullName;
        [DataMember]
        public string jenisUsaha;
        [DataMember]
        public long rencanaPlafon;
        [DataMember]
        public string mobilePhone;
        [DataMember]
        public string officePhone;
        [DataMember]
        public string residentialPhone;
        [DataMember]
        public string kodyaKabBusiness;
        [DataMember]
        public string kecamatanBusiness;
        [DataMember]
        public string kelurahanBusiness;
        [DataMember]
        public string prospectType;
        [DataMember]
        public string debtorType;
        [DataMember]
        public string channeling;
        [DataMember]
        public string zipCodeBusiness;
        [DataMember]
        public string addressBusiness;
        [DataMember]
        public string eksternal;
        [DataMember]
        public string referensi;
        [DataMember]
        public int avgExpense;
        [DataMember]
        public string dateOfBirth;
        [DataMember]
        public string maritalStatus;
        [DataMember]
        public string spouseName;
        [DataMember]
        public int jmlTanggungan;
        [DataMember]
        public string productCode;
        [DataMember]
        public string ktpNo;
        [DataMember]
        public string ktpName;
        [DataMember]
        public string ktpAddress;
        [DataMember]
        public string entryBy;
        [DataMember]
        public string entryDate;
        [DataMember]
        public string updateBy;
        [DataMember]
        public string updateDate;
        [DataMember]
        public string aoCode;
        [DataMember]
        public string unitCode;
        [DataMember]
        public string progressStatus;
        [DataMember]
        public string umDecision;
        [DataMember]
        public string umDecisionNote;
        [DataMember]
        public string umDecisionDate;
        [DataMember]
        public string reputasiDebitur;
        [DataMember]
        public string coAssigned;
        [DataMember]
        public string alternateFor;
        [DataMember]
        public string notInterestedReason;
        [DataMember]
        public string guCloseDeal;
        [DataMember]
        public string isUpload;
        [DataMember]
        public string uploadBy;
        [DataMember]
        public string uploadDate;
        [DataMember]
        public string isDeleted;
        [DataMember]
        public string isOfflineEntry;
        [DataMember]
        public int omzet;
        [DataMember]
        public string loanNumber;
        [DataMember]
        public int CIFNumber;
        [DataMember]
        public long omzet1;
        [DataMember]
        public long omzet2;
        [DataMember]
        public long omzet3;
        [DataMember]
        public long omzet4;
        [DataMember]
        public long omzet5;
        [DataMember]
        public long omzet6;
        [DataMember]
        public long omzet7;
        [DataMember]
        public long omzet8;
        [DataMember]
        public long omzet9;
        [DataMember]
        public long omzet10;
        [DataMember]
        public long omzet11;
        [DataMember]
        public long omzet12;
        [DataMember]
        public long omzetAverage;
        [DataMember]
        public string sourceCode;
        [DataMember]
        public string pipelineStartTime;
        [DataMember]
        public string pipelineFinishTime;
        [DataMember]
        public string prospectStartTime;
        [DataMember]
        public string prospectFinishTime;
        [DataMember]
        public string guFup;
        [DataMember]
        public string isPipelineKP;
        [DataMember]
        public string rowID;
        [DataMember]
        public int flagFreeze;
        [DataMember]
        public string placeOfBirth;
        [DataMember]
        public string nip;
        [DataMember]
        public string karakter;
    }

    [DataContract]
    public class MobileProspectVisitInfo
    {
        [DataMember]
        public string prospectID;
        [DataMember]
        public int visitNo;
        [DataMember]
        public string dateVisitPlan;
        [DataMember]
        public string dateVisitActual;
        [DataMember]
        public string joinFieldWork;
        [DataMember]
        public string objectiveOfVisit;
        [DataMember]
        public string resultOfVisit;
        [DataMember]
        public string remark;
        [DataMember]
        public string entryBy;
        [DataMember]
        public string entryDate;
        [DataMember]
        public string updateBy;
        [DataMember]
        public string updateDate;
        [DataMember]
        public string uploadBy;
        [DataMember]
        public string uploadDate;
        [DataMember]
        public string isUpload;
        [DataMember]
        public string isDeleted;
    }

    [DataContract]
    public class MobileProspectProgressInfo
    {
        [DataMember]
        public string prospectID;
        [DataMember]
        public string progressStatus;
        [DataMember]
        public string entryBy;
        [DataMember]
        public string entryDate;
        [DataMember]
        public string updateBy;
        [DataMember]
        public string updateDate;
        [DataMember]
        public string isUpload;
        [DataMember]
        public string uploadBy;
        [DataMember]
        public string uploadDate;
    }

    [DataContract]
    public class MobileMaintenanceVisitInfo
    {
        [DataMember]
        public string id;
        [DataMember]
        public string prospect_id;
        [DataMember]
        public string aoCode;
        [DataMember]
        public string unitCode;
        [DataMember]
        public string dateVisit;
        [DataMember]
        public string noAccount;
        [DataMember]
        public string debtorName;
        [DataMember]
        public string objectiveOfVisit;
        [DataMember]
        public string entryBy;
        [DataMember]
        public string entryDate;
        [DataMember]
        public string updateBy;
        [DataMember]
        public string updateDate;
        [DataMember]
        public string uploadBy;
        [DataMember]
        public string uploadDate;
        [DataMember]
        public string isDeleted;
        [DataMember]
        public string isUpload;
        [DataMember]
        public string recordId;
        [DataMember]
        public string expiryDate;
        [DataMember]
        public string extendDate;
        [DataMember]
        public string extendReason;
        [DataMember]
        public string batchId;
        [DataMember]
        public string proofCall;
        [DataMember]
        public string hasilProofCall;
        [DataMember]
        public string cif;
        [DataMember]
        public string kontakLog;
        [DataMember]
        public string jenis_kontak;
        [DataMember]
        public string dataType;
        [DataMember]
        public string extendDate2;
        [DataMember]
        public string nipTeller;
    }

    [DataContract]
    public class MobileHolidayInfo
    {
        [DataMember]
        public string holidayDate;
        [DataMember]
        public string holidayName;
        [DataMember]
        public string isActive;
    }

    [DataContract]
    public class MobileUpdateTableInfo
    {
        public string tableName { get; set; }
        public string fieldName { get; set; }
        public string dataType { get; set; }
        public string isTableNew { get; set; }
    }

    [DataContract]
    public class MobileExternalMappingInfo
    {
        [DataMember]
        public string externalID;
        [DataMember]
        public string channelID;
        [DataMember]
        public string isActive;
    }

    [DataContract]
    public class MobileFlagFreezeInfo
    {
        [DataMember]
        public int flag;
        [DataMember]
        public string field;
        [DataMember]
        public string enabled;
        [DataMember]
        public string isActive;
    }

    [DataContract]
    public class MobileProspectRefferalInfo
    {
        [DataMember]
        public string refferalID;
        [DataMember]
        public string fullName;
        [DataMember]
        public string jenisUsaha;
        [DataMember]
        public string jenisUsahaDesc;
        [DataMember]
        public string officePhone;
        [DataMember]
        public long rencanaPlafon;
        [DataMember]
        public string addressBusiness;
        [DataMember]
        public string unitCodeSource;
        [DataMember]
        public string unitCodeDest;
        [DataMember]
        public string aoCodeOrig;
        [DataMember]
        public string assignedTo;
        [DataMember]
        public string assignedDate;
    }

    [DataContract]
    public class MobileBranchInfo
    {
        [DataMember]
        public string branchID;
        [DataMember]
        public string branchName;
    }

    [DataContract]
    public class MobileSendRefferalFundingInfo
    {
        [DataMember]
        public string refID;
        [DataMember]
        public string fullName;
        [DataMember]
        public string productCode;
        [DataMember]
        public string officePhone;
        [DataMember]
        public string rencanaSetoran;
        [DataMember]
        public string addressBussiness;
        [DataMember]
        public string unitCodeSource;
        [DataMember]
        public string unitCodeDest;
        [DataMember]
        public string aoCodeOrigin;
        [DataMember]
        public string res100;
        [DataMember]
        public string entryBy;
        [DataMember]
        public string entryDate;
        [DataMember]
        public string updateBy;
        [DataMember]
        public string updateDate;
        [DataMember]
        public string uploadBy;
        [DataMember]
        public string uploadDate;
        [DataMember]
        public string isDownloaded;
        [DataMember]
        public string downloadBy;
        [DataMember]
        public string downloadDate;
        [DataMember]
        public string isAssigned;
        [DataMember]
        public string assignTo;
        [DataMember]
        public string assignDate;

    }

    [DataContract]
    public class MobileSendRefferalLendingInfo
    {
        [DataMember]
        public string refID;
        [DataMember]
        public string fullName;
        [DataMember]
        public string jenisUsaha;
        [DataMember]
        public string officePhone;
        [DataMember]
        public string rencanaPlafon;
        [DataMember]
        public string addressBussiness;
        [DataMember]
        public string unitCodeSource;
        [DataMember]
        public string unitCodeDest;
        [DataMember]
        public string aoCodeOrigin;
        [DataMember]
        public string entryBy;
        [DataMember]
        public string entryDate;
        [DataMember]
        public string updateBy;
        [DataMember]
        public string updateDate;
        [DataMember]
        public string uploadBy;
        [DataMember]
        public string uploadDate;
        [DataMember]
        public string isDownloaded;
        [DataMember]
        public string downloadBy;
        [DataMember]
        public string downloadDate;
        [DataMember]
        public string isAssigned;
        [DataMember]
        public string assignTo;
        [DataMember]
        public string assignDate;

    }

    [DataContract]
    public class MobileMaintenanceVisitKpInfo
    {
        [DataMember]
        public string id;
        [DataMember]
        public string prospect_id;
        [DataMember]
        public string aoCode;
        [DataMember]
        public string unitCode;
        [DataMember]
        public string dateVisit;
        [DataMember]
        public string noAccount;
        [DataMember]
        public string debtorName;
        [DataMember]
        public string objectiveOfVisit;
        [DataMember]
        public string entryBy;
        [DataMember]
        public string entryDate;
        [DataMember]
        public string updateBy;
        [DataMember]
        public string updateDate;
        [DataMember]
        public string uploadBy;
        [DataMember]
        public string uploadDate;
        [DataMember]
        public string isDeleted;
        [DataMember]
        public string isUpload;
        [DataMember]
        public string recordId;
        [DataMember]
        public string expiryDate;
        [DataMember]
        public string extendDate;
        [DataMember]
        public string extendReason;
        [DataMember]
        public string batchId;
        [DataMember]
        public string proofCall;
        [DataMember]
        public string hasilProofCall;
        [DataMember]
        public string cif;
        [DataMember]
        public string kontakLog;
        [DataMember]
        public string jenis_kontak;
        [DataMember]
        public string dataType;
        [DataMember]
        public string extendDate2;
        [DataMember]
        public string nipTeller;
        [DataMember]
        public string isDownloaded;
    }
}
